cancel
Showing results for
Did you mean:
Highlighted

## 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

## 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])))

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.
2 REPLIES 2
Highlighted
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])))

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.
Highlighted
Post Patron

## Re: Cumulative SUM using Rank, NOT Dates

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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

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

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### 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