RUNNING SUMS USING A RANKING CALCULATED MEASURE

[TheChaks]

Hello Guyz,

I made a ranking using Rankx based on total sales per client :

Rang CA/CLT = rankx(ALL(V_CUBE[Client]);CALCULATE(sum(V_CUBE[CA HT]));;DESC)

The calculated measure works well and for each client now I have his total sales and his rank

Now I want to make a running sum on the total sales using the rank I computed previously.

I applied couple of solutions I found on the forum with NO result.

Can anyone help?

Thanks in advance

[Ashish_Mathur]

Hi,

Please share a dataset and show the expected result.

[TheChaks]

Hey,

Thanks for helping.

Here is what I have:

 

The ranking you see come from the measure I showed in my previous message.

The expected result is as follow;

[Ashish_Mathur]

Hi,

Write these measures

Revenue = SUM(Data[Total Sales])

Rank = if(HASONEVALUE(Data[Client]),RANKX(ALL(Data[Client]),[Revenue]),BLANK())

Cumulative revenue = if(HASONEVALUE(Data[Client]),SUMX(TOPN([Rank],CALCULATETABLE(VALUES(Data[Client]),ALL(Data[Client])),[Revenue]),[Revenue]),BLANK())

Drag Client. Revenue and Cumulative revenue to the visual and sort the Revenue field in descending order.

Hope this helps.

[TheChaks]

FYI the data source table is V_CUBE and the sales column is CA_HT

[TomMartens · Accepted solution]

Hey @TheChaks,

first I created a simple measure for the column "CA HT", the final measure will reference this base measure. Besides the somewhat shorter writing, it also ensures the implicit Context Transition (from Filter Context to Row Context), this is the base measure:

ms CA HT = SUM(V_Cube[CA HT])

The final measure looks like this:

ms CA HT **bleep** = 
var rankofcurrentrow = [Rang CA/CLT]
return
IF(
    HASONEVALUE('V_Cube'[Client])
    ,// one value client
    GROUPBY(
        FILTER(
            ADDCOLUMNS(
                ALL(V_Cube[Client])    
            ,"theRank", [Rang CA/CLT]
            ,"theAmount", [ms CA HT]
            )
            ,[theRank] <= rankofcurrentrow
        )
        ,"value", SUMX(CURRENTGROUP(), [theAmount])
    )
    ,// total
    GROUPBY(
 //       FILTER(
            ADDCOLUMNS(
                ALL(V_Cube[Client])    
            ,"theRank", [Rang CA/CLT]
            ,"theAmount", [ms CA HT]
            )
 //           ,[theRank] <= rankofcurrentrow
 //       )
        ,"value", SUMX(CURRENTGROUP(), [theAmount])
    )
)

It may look like more complex than it actually is, this is just because I repeat almost the same part for the Total Row, this can be much more simplified (at least I'm sure) depending on your requirements and your data model. Here I omitted the filtering, this allows showing the correct value for the Total row.

Nevertheless, using the final measure in a table will create this:

Hopefully, this is what you are looking for!

Regards,

Tom

[TheChaks]

Hey Tom !

Your solution works perfect ! Thanks

As a beginner, it's indeed quite difficult for me to understand it all.... I'll do my best 🙂

Is there any other simple way to get the same result?