cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Cumulative SUM using Rank, NOT Dates

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
Highlighted
Microsoft
Microsoft

Re: Cumulative SUM using Rank, NOT Dates

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

Highlighted
Post Patron
Post Patron

Re: Cumulative SUM using Rank, NOT Dates

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors