I have a dataset like the screenshot below. This dataset shows the training topics, session date, and participant name. Column C target_audience field shows if the participant is a target audience.
I wanted to calculate the cumulative distinct running sum of target audience. I have tried below calculated field but the result is wrong
The result I get is below. I have highlighted the desired result in column G.
For example, for topic food, there are 4 distinct eligible participants (Tom, Alice, James, Susan). Alice and Susan are target audience. Alice joined the session in Jan and Susan joined in April. So, the cumulative distinct running total for target audience by month is 1 for Jan (Alice); no target audience joined in Feb/Mar, so the cumulative total is still 1 for Feb and Mar; and 2 for Apr (Alice+Susan).
I know one way is to remove the {target_audience}=1 field in the calculation and add this as a filter, but I want to add other calculation fields in the same table so I would like to include {target_audience}=1 in the calculated field, instead of as a filter.
Is there any way to calculate it in QuickSight?
Thank you!