Forum Discussion
Cumulative Total over a measure
Hello everyone!
I have to create a cumulative value (MG%Cumulative) according to a rank measure (Rank).
I had some trouble using the standard calculate-filter solution cause the filter function requires a column from the underlying table, while I have to filter according to a measure.
| Item | Revenue | Rank | Mg% | Mg%Cumulative |
| 101 | 100.000 | 1 | 40% | 40% |
| 103 | 80.000 | 2 | 30% | 70% |
| 102 | 50.000 | 3 | 20% | 90% |
| 105 | 10.000 | 4 | 10% | 100% |
NOTE: The pbix is created with a direct query on a SSAS Tabular cube and I've no acces to the underlying tables.
Thus I should resolve the issue just using measures.
Thanks in advance,
Angelo
Try this MEASURE in ITEMSTABLE
Revenue Cumulative = VAR CurrentRow = [Rank] RETURN SUMX ( FILTER ( ALLSELECTED ( ItemsTable[Item] ), [Rank] <= CurrentRow ), [Revenue] )Thanks to Marco Russo I found a solution that avoid the uses of the VAR function!
https://www.sqlbi.com/blog/marco/2014/02/20/the-cumulative-total-dax-pattern/#comment-3722046622
Thanks everyone,
Angelo
6 Replies
Hi angelocecinati,
Can you share more detailed contents about your situation?
I'm not so sure how MG measure calculated, it will be help if you share measure formula and expected result.Regards,
Xiaoxin Sheng
Hi Anonymous,
Sure! The MG measure is not so important. We can simplify further the example in this way:
Item Revenue Rank Revenue Cumulate 101 100.000 1 100.000 103 80.000 2 180.000 102 50.000 3 230.000 105 10.000 4 240.000 I want to compute the Revenue Cumulate according to the Rank measure.
The Revenue is a measure coming directly from the cube. And I've not visibility about its computation.
The Rank is a measure computed as follow:
Rank = if (HASONEVALUE
(ItemsTable[Items])
,RANKX(ALL(ItemsTable[Items])
,[Revenue],,,Dense)
,BLANK()
)
The Revenue Cumulate has to show the highest revenue in the first row, the sum of the first and the second highest in the second row and so on.
I hope to be clear,
Thanks a lot,
Angelo
- Zubair_Muhammad
Community Champion
Try this MEASURE in ITEMSTABLE
Revenue Cumulative = VAR CurrentRow = [Rank] RETURN SUMX ( FILTER ( ALLSELECTED ( ItemsTable[Item] ), [Rank] <= CurrentRow ), [Revenue] )
