Forum Discussion
Running total doesn't add up
Hello, I'm really new to Power BI, I'm trying to display the running total of distinct values broken down by months.
Here's what I have, it's some mock data, just some dates and email addresses. As you can see the email '[email protected]' is the only email repeated, it shows up 3 times across 2 months
I've added a measure
Distinct Email Count = DISTINCTCOUNT(MOCK_DATA[email])
and a running total measure
Distinct Email Running Total =
CALCULATE(
[Distinct Email Count],
FILTER(
ALLSELECTED('Calendar2'),
'Calendar2'[YearMonth] <= MAX('Calendar2'[YearMonth])
)
)
The table below seems to show the correct values for the "Distinct Email Count" measure, however for the "Distinct Email Running Total" measure, I'd expect the month 2 to be 9 and not 8. I'm guessing my formula is wrong as it seems to be giving me the entire distinct count in month 2 instead of (3+6)=9
Any help for a newbie would be greatly appreciated
Hi dmq147,
As you noted, one of the emails shows up in both months. Therefore, if you look at both months as a whole for your running total calculation for YearMonth of 2024-03, there are only 8 distinct emails. Your DAX is doing exactly what you're asking it to do.
What you're looking for though is to treat each month as a distinct entity, then add those together. Something like this should work:
Distinct Email Running Total = VAR SelYearMonth = MAX ( 'Calendar2'[YearMonth] ) VAR YearMonths = FILTER ( ALLSELECTED ( 'Calendar2'[YearMonth] ), 'Calendar2'[YearMonth] <= SelYearMonth ) VAR Result = SUMX ( YearMonths, [Distinct Email Count] ) RETURN Result
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
3 Replies
Thanks so much, just tested this now and it works but also it makes sense to me why it works
Hi dmq147,
As you noted, one of the emails shows up in both months. Therefore, if you look at both months as a whole for your running total calculation for YearMonth of 2024-03, there are only 8 distinct emails. Your DAX is doing exactly what you're asking it to do.
What you're looking for though is to treat each month as a distinct entity, then add those together. Something like this should work:
Distinct Email Running Total = VAR SelYearMonth = MAX ( 'Calendar2'[YearMonth] ) VAR YearMonths = FILTER ( ALLSELECTED ( 'Calendar2'[YearMonth] ), 'Calendar2'[YearMonth] <= SelYearMonth ) VAR Result = SUMX ( YearMonths, [Distinct Email Count] ) RETURN Result
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
