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!
@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 )
Hi @Anonymous ,
Could it be you typed > instead of >=?
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?
@Anonymous , 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 the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.