Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |