Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
100 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |