cancel
Showing results for
Did you mean:
Helper I

## Running total based on ranking index

Hi all!

I'm looking for a way to calculate a Running total. So from what I read before, I tried to create a Ranking index with this formula:

Rank = RANKX(ALLSELECTED(Division[Product code]),CALCULATE(([Price (€/kg)])),,ASC,Dense)

And that works pretty well because I have the result on the table here :

 MonthYear Product code Sales (t) Price (€/t) Rank Running total sales 01-05-19 497388 3233,9 167,8 1 01-05-19 497407 2486,1 170,2 2 01-05-19 497401 1076,9 171,4 3 01-05-19 497419 1044,1 179,3 4 01-05-19 506324 1650,6 198,4 5 01-05-19 497404 747,5 200,1 6 01-05-19 497466 6374,4 203,7 7 01-05-19 497437 762,5 270,6 8 01-05-19 501755 840,7 272,3 9 01-05-19 497413 124,3 275,4 10 01-05-19 502105 1274,7 355,5 11 01-05-19 497457 203,7 629,7 12

But then when I try my formula for the Running Total Sales :

``````Running total sales =
Var IndexRank  = [Rank]

Return
SUMX( FILTER(
SUMMARIZE( Division, Division[Product code],
"Sales" , Sales[Sales (kg)],
"Sales Ranking" , RANKX( ALL(Division) , [Sales (kg)], , ASC) ),
[Sales Ranking] <= IndexRank ),
[Sales] )``````

It just shows nothing as displayed on the table above ...

So basically what I would like to see is the running total of the Sales column based on the Rank which is related to the column  [Price (€/t)].

Does someone see the problem here?

Thanks a lot for your help!

6 REPLIES 6
Super User II

@pbiBXL1234  Is this what you want to achieve? PBI file is uploaded below my signature.

``````Rank =
RANKX (
SUMMARIZE (
ALLSELECTED ( Division ),
Division[MonthYear],
Division[Product code]
),
CALCULATE ( SUM ( Division[Price (€/t)] ) ),
,
ASC,
DENSE
)``````
``````Running total sales =
IF (
ISINSCOPE ( Division[Product code] ) && ISINSCOPE ( Division[MonthYear] ),
VAR CurrentRank = [Rank]
VAR SummarizeDivision =
CALCULATETABLE (
SUMMARIZE ( Division, Division[Product code], Division[MonthYear] ),
ALLSELECTED ( Division )
)
VAR DivisionSales =
ADDCOLUMNS ( SummarizeDivision, "@Sales", [Total Sales], "@Rank", [Rank] )
VAR Result =
SUMX ( FILTER ( DivisionSales, [@Rank] <= CurrentRank ), [@Sales] )
RETURN
Result
)``````

Thank you,
Antriksh Sharma
Super User I

Hi @pbiBXL1234 ,

Could it be you typed > instead of >=?

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain
Super User I

Hi @pbiBXL1234 ,

If I understood correctly your requirement you are looking for this:

``````Running total sales =
VAR IndexRank = [Rank]
RETURN
CALCULATE (
SUM ( Division[Sales (t)] ),
FILTER (
ALL ( Division[Product code] ),
IndexRank
>= RANKX ( ALLSELECTED ( Division[Product code] ), [sum price],, ASC, DENSE )
)
)``````

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain
Helper I

Hi @Payeras_BI ,

Indeed, you understood correctly as it displays what I am looking for but there is one problem and I don't understand where it comes from: there is a row offset as you can see on the screenshot below:

Also I don't know if this is useful but the column "Price (€/t)" is also a measure.

Do you know where this might come from?

Thanks again,

Super User IV

@pbiBXL1234 , Try a new measure like

SUMMARIZE( Division, Division[Product code],
"Sales" , Sales[Sales (kg)],
"Sales Ranking" , RANKX( ALL(Division) , [Sales (kg)], , ASC) ),
"Cumm" , calculate(sum([Sales]), filter( Division, [Sales Ranking] <= max([Sales Ranking]) ))),
[Cumm] )

Proud to be a Super User!

Helper I

Hi @amitchandak ,

It is not working as I need my Rank to be a measure and not a column because I have a lot of datas (the table here is just a filtered example of my datas) and I need this ranking to be dynamic as I am filtering on date, types of products and many other fields so the only way of doing this is with a measure and Max function only accepts a column reference.

Do you know another way of doing this with Ranking being a measure ?

Thanksss

Announcements