Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MWinter225
Advocate IV
Advocate IV

Cumulative SUM using Rank, NOT Dates

Hi All,

 

I've been researching a while about how to do this and I feel like I'm getting close. I'm pretty familiar with DAX but feel much more comfortable in languages used by Tableau and Spotfire.

 

What I'm trying to do:

  1. Create a cumulative/running sum of Media cost that aggregates from the TV station that has the highest MER (a ratio where MER=Sales/Spend)and work our way down from the first highest MER station  to the second highest to the third highest … etc.
    1. Below we have a table with Station, Media Cost for that station, and MER sorted descending from the highest MER. I need to write a calculation that sums the values according to the rank of the highest MER station.

Station

Media Cost

MER (sorted desc)

Cumulative Media Cost

B

23.25

4.3

23.25 (Media Cost of station B)

D

57.97

3.45

81.22 (Media Cost of station B+D)

A

44.78

3.35

126 (Media cost of station B+D+A)

C

266.67

1.5

392.67 (Media Cost of station B+D+A+C)

 

What I have done so far:

  1. I have created a MEASURE that dynamically ranks each station based on the highest MER in the matrix/text table:
    Ranking MER=
    MINX (
    FILTER (
    SELECTCOLUMNS (
    ALLSELECTED ( 'Rank Report' ),
    "index", 'Rank Report'[Index],
    "rank", RANKX ( ALLSELECTED ( 'Rank Report' ), 'Rank Report'[MER],,DESC, Dense )
    ),
    [index] = MAX ( 'Rank Report'[Index] )
    ),
    [rank]
    )
     

So this gives me:

Station

Client Revenue

Media Cost

MER (sorted desc)

Top Rank Measure

B

100

23.25

4.3

1

D

200

57.97

3.45

2

A

150

44.78

3.35

3

C

400

266.67

1.5

4

               

I thought then I could come up with some cumulative calculation to sum Media Cost but I’ve hit a wall. The closest that I’ve gotten were these calculations giving me Media Cost for the same row (these are inspired from searcing online):

 

 

Cumulative Cost =
CALCULATE(SUM('Rank Report'[Media Cost]),
                FILTER(ALL('Rank Report'[Station]),
                                [Top Rank Measure]<=100
                                )
)

 

 And

Cumulative Cost 2= 
CALCULATE ( 
SUM('Rank Report'[Media Cost]),
ALLSELECTED('Rank Report'[Station])
)

Either one gives me this:

Station

Client Revenue

Media Cost

MER (sorted desc)

Top Rank Measure

Cumulative Cost

B

100

23.25

4.3

1

23.25

D

200

57.97

3.45

2

57.97

A

150

44.78

3.35

3

44.78

C

400

266.67

1.5

4

266.67

 

At first I tried to just use the Template for a Running Total for Dates and fill in my columns but I got an error saying: 

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

The basic formula for a running total is:
Running Total COLUMN =
CALCULATE (
    SUM ( ‘TableName’[ColumnYouWantToAccumulate] ),
    ALL ( ‘TableName’ ),
    'TableName'[DateColumn] <= EARLIER ( 'TableName'[DateColumn] )
)

I tried replacing the dates with my Top Ranking Measure  like this:
 
Cumulative Cost=
CALCULATE (
    SUM ( 'Rank Report'[Media Cost] ),
    ALL ( 'Rank Report' ),
    [Top Rank Measure] <= EARLIER ( [Top Rank Measure] )
)

How can I get a cumulative sum starting from the top row with the highest MER and descned down keeping a running total of Media Cost?

 

Let me know if you need any more info. 

 

Thanks,

Matt

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

Hi @MWinter225,

In your scenario, you can create a rank column instead of measure using DAX below.

RankColumn = RANKX(FILTER('Rank Report',NOT(ISBLANK('Rank Report'[MER]))),'Rank Report'[MER],,0,Dense)

 

Then, create the following measures.

SumCost = SUM('Rank Report'[Media Cost])
Cumulative Cost = CALCULATE([SumCost],FILTER(ALL('Rank Report'),'Rank Report'[RankColumn]<=MAX('Rank Report'[RankColumn])))
1.PNG

 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yuezhe-msft
Employee
Employee

Hi @MWinter225,

In your scenario, you can create a rank column instead of measure using DAX below.

RankColumn = RANKX(FILTER('Rank Report',NOT(ISBLANK('Rank Report'[MER]))),'Rank Report'[MER],,0,Dense)

 

Then, create the following measures.

SumCost = SUM('Rank Report'[Media Cost])
Cumulative Cost = CALCULATE([SumCost],FILTER(ALL('Rank Report'),'Rank Report'[RankColumn]<=MAX('Rank Report'[RankColumn])))
1.PNG

 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yuezhe-msft

 

I am trying to implement something similar. I want to calculate the running sum for product sub-categories based on sales, but I have a slicer for product categories which targets a grid with data for Product sub-category and sales. The solution mentioned by you works if I have a single product category selected in the slicer. If i "Select All", the rank and cumulative sum doesn't get calculated correctly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.