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|
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!
@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 )
Hi @pbiBXL1234 ,
Could it be you typed > instead of >=?
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 ) ) )
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?
@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]) ))),
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 ?
Check out new user group experience and if you are a leader please create your group
Click here to read more about the April 2021 Updates!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.
See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.