Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
@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?
Thanks again,
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |