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.
Hello there! I have a running total formula that is performing in the opposite way as expected (needed). I have tried changing it in many ways but it either doesn't make sense or just gives zeros. Right now the output and the formula are as follow:
Running Total % =
var rankcheck = RANKX ( ALL ( 'ds05 - Vendas'[Cliente] ), [Valor2],, ASC )
var runningTotal = CALCULATE( [Valor2], FILTER( ALL( 'ds05 - Vendas'[Cliente] ), RANKX ( ALL ( 'ds05 - Vendas'[Cliente] ), [Valor2], , ASC ) <= rankcheck ))
var totalSales = CALCULATE( [Valor2], ALL( 'ds05 - Vendas'[Cliente] ))
return
DIVIDE(runningTotal,totalSales,0)
The expected result is:
Column1 | Column2 | Column3 |
619,42 | 21,89% | 21,89% |
588,50 | 20,80% | 42,69% |
287,10 | 10,15% | 52,84% |
Thanks in advance for any kind of help!
Solved! Go to Solution.
Have you tried changing the sorting order on the rankx?
Running Total % =
VAR rankcheck =
RANKX ( ALL ( 'ds05 - Vendas'[Cliente] ), [Valor2],, DESC )
VAR runningTotal =
CALCULATE (
[Valor2],
FILTER (
ALL ( 'ds05 - Vendas'[Cliente] ),
RANKX ( ALL ( 'ds05 - Vendas'[Cliente] ), [Valor2],, DESC ) <= rankcheck
)
)
VAR totalSales =
CALCULATE ( [Valor2], ALL ( 'ds05 - Vendas'[Cliente] ) )
RETURN
DIVIDE ( runningTotal, totalSales, 0 )
If this does not work can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
Have you tried changing the sorting order on the rankx?
Running Total % =
VAR rankcheck =
RANKX ( ALL ( 'ds05 - Vendas'[Cliente] ), [Valor2],, DESC )
VAR runningTotal =
CALCULATE (
[Valor2],
FILTER (
ALL ( 'ds05 - Vendas'[Cliente] ),
RANKX ( ALL ( 'ds05 - Vendas'[Cliente] ), [Valor2],, DESC ) <= rankcheck
)
)
VAR totalSales =
CALCULATE ( [Valor2], ALL ( 'ds05 - Vendas'[Cliente] ) )
RETURN
DIVIDE ( runningTotal, totalSales, 0 )
If this does not work can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix I have and it never worked until now so I guess it must have been some filter context had in place which has since been deleted! Thanks for your help!
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |