Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello To all
I'm struggling with a cumulative total by Month that I need.
I need to show by month the total number of rows of a given state but adding the ones of the previous month.
I have the following table:
id status month
1 x 1
2 x 1
3 x 2
4 x 3
5 x 3
6 x 3
7 x 4
So in this case in month 1 I want to show 2, on 2 show 3 (2+1), on 3 show 6 (2+1+3) and so on.
I'm using the following formula:
Cumulative_actual = CALCULATE(COUNT(ChangeStatus[id]);
FILTER(ChangeStatus;ChangeStatus[Month] <= MAX(ChangeStatus[Month])))
But it is not adding. I also tried using SUM, instead of count and the result was the same.
Regards
Solved! Go to Solution.
Hello
@v-caliao-msft, I tried with allexcept and it worked.
This was the formula:
Cumulative_actual = CALCULATE(DISTINCTCOUNT(ChangeStatus[id]);
FILTER(ALLEXCEPT(ChangeStatus;ChangeStatus[MyFilterColumn]);
ChangeStatus[Month] <= MAX(ChangeStatus[Month])))
This way the result was correctly calculated.
Thanks.
@Anonymous
Hi, try adding ALL Function
Cumulative_actual = CALCULATE(COUNT(ChangeStatus[id]);
FILTER(ALL(ChangeStatus);ChangeStatus[Month] <= MAX(ChangeStatus[Month])))
Thanks, It seems ALL helped in the rigth direction but...
If I use a simple graphic of state by month I get these values:
4 10
5 5267
6 6
But the cumulative:
4 6155
5 18693
6 20067
It seems it is not filtering... due to the ALL I presume since it "cleans" the filters.
Should I use ALLExcept?
@Anonymous,
Could you please share more information about your data and expected result? So that we can make further analysis.
Regards,
Charlie Liao
Hello
@v-caliao-msft, I tried with allexcept and it worked.
This was the formula:
Cumulative_actual = CALCULATE(DISTINCTCOUNT(ChangeStatus[id]);
FILTER(ALLEXCEPT(ChangeStatus;ChangeStatus[MyFilterColumn]);
ChangeStatus[Month] <= MAX(ChangeStatus[Month])))
This way the result was correctly calculated.
Thanks.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |