Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 PowerQu...
Need Help
Tips and Tricks
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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.

  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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 _sumx

     

    Result:


    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 :

https://we.tl/t-G3LVoeELhq

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

 

v-angzheng-msft's avatar
v-angzheng-msft
Community Support
4 years ago

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 _sumx

 

Result:


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.