Forum Discussion
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:
| 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
Payeras_BISolution Sage
Hi Anonymous ,
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 ) ) )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,
- AntrikshSharma
Community Champion
Anonymous 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 ) - amitchandak
Super User
Anonymous , Try a new measure like
SUMX( ADDCOLUMNS(
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] )Hi amitchandak ,
Thanks for the quick reply.
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
- Payeras_BI
Hi Anonymous ,
Could it be you typed > instead of >=?
