cancel
Showing results for
Did you mean:
Super User

## 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!

1 ACCEPTED SOLUTION
Super User

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

2 REPLIES 2
Super User

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

Super User

@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!

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors