Forum Discussion
Running total is reversed
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!
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.
2 Replies
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.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!
