Hi All,
I have a SQL data source which has multiple columns and many rows, I want to get cumulative total. I have tried several Dax formulas, but nothing is giving me desired result. For my requirement out of many columns i have to use RootCause and MissedCount column and I want to calculate Cumulative total % on descending sort order of MissedCount.
I am able to get cumulative total on sorting order of RootCause using below formula but i want to calculate Cumulative total % based on sorting order of MissedCount.
Cumlative % =
var MissedC=CALCULATE(SUM(Table[MissedCount]),FILTER(ALLSELECTED(Table[RootCause]),ISONORAFTER(Table[RootCause],Max(Table[RootCause]),DESC)))
var totalMissedC=CALCULATE(SUM(Table[MissedCount]),ALLSELECTED(Table))
return DIVIDE(MissedC,totalMissedC)
Expected Cumulative %-
Thanks
Solved! Go to Solution.
Hi @NSC7 ,
Please try below steps:
1. below is my test table
Table:
2. create measure with below dax formula
Measure =
VAR cur_mc =
SELECTEDVALUE ( 'Table'[Missed Count] )
VAR tmp =
FILTER ( ALL ( 'Table' ), [Missed Count] >= cur_mc )
RETURN
SUMX ( tmp, [Total %] )
3. add a table visual with fields and measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @NSC7 ,
Please try below steps:
1. below is my test table
Table:
2. create measure with below dax formula
Measure =
VAR cur_mc =
SELECTEDVALUE ( 'Table'[Missed Count] )
VAR tmp =
FILTER ( ALL ( 'Table' ), [Missed Count] >= cur_mc )
RETURN
SUMX ( tmp, [Total %] )
3. add a table visual with fields and measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
114 | |
59 | |
57 | |
44 | |
41 |
User | Count |
---|---|
116 | |
66 | |
64 | |
64 | |
46 |