Forum Discussion
Cumulative total Column with dynamic ranking Column
Hello all,
I hope you are all fine.
I am currently trying to create a cumulative column, based on a dynamic ranking.
I have tested a few things.
First, I have created an Index using PowerQuery (Column Index) and computed a formula (Cumulative TO 0).
But, if I apply a filter, the cumulative amount per line will stay the same - which is quite expecting as per my research.
So I have created a column using DAX in order to get the same Sorting/Ranking (Ranking 1) and I am computing the cumulative based on that ranking (Cumulative 1). Unfortunately, same as with the Index, the sorting is not changing based on filter.
Ranking 1 formula :
Ranking 1 = RANKX(all('Tesson''s Curve'),'Tesson''s Curve'[NM%],,DESC)
Cumulative TO 1 = CALCULATE(
Sum('Tesson''s Curve'[TO ]),
FILTER(ALLSELECTED('Tesson''s Curve'),
'Tesson''s Curve'[Ranking 1]<=EARLIER('Tesson''s Curve'[Ranking 1])
))
Finaly, I have tried a third formula in order to create the dynamic ranking.
Ranking 3 =
MINX(
FILTER(
SELECTCOLUMNS(
ALLSELECTED('Tesson''s Curve'),
"index",'Tesson''s Curve'[Index],
"rank",RANKX(ALLSELECTED('Tesson''s Curve'),'Tesson''s Curve'[NM%],,DESC,Dense)
),
[index]=MAX('Tesson''s Curve'[Index])
),
[rank]
)
It's working fine with a measure - as you can see above, but it's totally wrong within a column - and I need to have my cumulative as a column so I need the dynamic ranking to work as a column.
I am getting a bit deseparated now ...
Do you have any idea on how to create a dynamic ranking column ?
Thanks for your help, I can share a sample of the report if it can help.
Kind regards,
Guillaume
Hi, Anonymous
Try this:RunningTotal = VAR _currank2Table = ADDCOLUMNS ( 'MAESTRO V2 - Tesson''s Curve', "_Ranking Ter", [Ranking Ter] ) RETURN SUMX ( FILTER ( ALL ( _currank2Table ), [_Ranking Ter] < [Ranking Ter] ), [TO ] )If this does not work, for a quick fix, consider sharing the dummy sample data and expected output.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi, Anonymous
Try this:_RunningTotal CST = //*********************************************************************************************************** // solution: only need one measure VAR _CURRENT_perNM=MAX('Tesson''s Curve'[NM%]) VAR _sumx=SUMX(FILTER(ALLSELECTED('Tesson''s Curve'),'Tesson''s Curve'[NM%]>=_CURRENT_perNM),[TO ]) return _sumxResult:
If it's just to get cumulative totals, you only need this one measure.
But you can use the ranked column sorting table, which, by the way, I recreated to be a more concise ranking measure hopefully useful to you.Please refer to the attachment below for details.
Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
Hi, Anonymous
Calculated columns are only calculated when you first define them and during a dataset refresh.Unlike a calculated column, measures are evaluated on the fly with every change in context. If you change a date filter from 2019 to 2020, all the measures will be calculated again. While this is nice for seeing context-based calculations in real-time.
Workarounds.
1. create dynamic ranking measure, as you did above.
2. Create cumulative totals as a measure instead of calculated column.Refer:
Power BI: Calculated Measures vs. Calculated Columns
Calculated Columns and Measures in DAX
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hello Dear v-angzheng-msft,
thanks for your reply.
I underestood now that only the ranking measure are calculated based on the filter applied.
But in that case, Is it possible to create a cumulative total using that ranking measure ?
That's one thing I am trying to do but I don't know how to do ...
If you have any idea, thanks for your help.
Kind regards,
Guillaume
Hi, Anonymous
sorry for the late reply.
Yes, you can create cumulative totals based on dynamic ranking measures, but as I mentioned above, cumulative totals also need to be a measure.Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Zeon Zheng,
I think i got the idea - you create a table that will have ranking, and we compute the cumululative total on that table.
However, it seems there is something wrong in the formula, since it isreturning me that :
"La fonction ALL attend une référence de table pour l'argument « 1 », mais c'est une expression de table qui a été utilisée." =>
"The function ALL needs a table reference for the the argument "1", but it's a table expression that has been used."
here is a link to the dummy sample :
If you have any idea on how to solve this trouble - i am unfortunately not good enough as of today to do it myself ....
Thanks in advance for your help.
Best regards,
Guillaume
Hi, Anonymous
Try this:_RunningTotal CST = //*********************************************************************************************************** // solution: only need one measure VAR _CURRENT_perNM=MAX('Tesson''s Curve'[NM%]) VAR _sumx=SUMX(FILTER(ALLSELECTED('Tesson''s Curve'),'Tesson''s Curve'[NM%]>=_CURRENT_perNM),[TO ]) return _sumxResult:
If it's just to get cumulative totals, you only need this one measure.
But you can use the ranked column sorting table, which, by the way, I recreated to be a more concise ranking measure hopefully useful to you.Please refer to the attachment below for details.
Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.