Running value is not working as expected

[Ramachandran]

Hi,

Im trying to get sales amount running value. it is working in descending order but not working in ascending order. can you please provide some suggestion?
Source Data

Expected Output

I used calculation is below

Running Value =

VAR PdtRank =

RANKX(
ALLSELECTED('Sales'[Product Category]),
[SalesAmount] ,,
ASC,
Dense
)

VAR RunningTotal =
CALCULATE(
[SalesAmount],
FILTER(
ALLSELECTED('Sales'[Product Category]),
PdtRank >= RANKX(
ALLSELECTED('Sales'[Product Category]),
[SalesAmount],,
ASC,
Dense

)
)
)

RETURN RunningTotal

I got blank value for all rows. how do I fix it?

thanks,

Ram

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Running sales amount: = 
VAR currentsalesamount = [Sales Amount Measure:]
RETURN
    CALCULATE (
        [Sales Amount Measure:],
        FILTER ( ALL ( Data ), [Sales Amount Measure:] <= currentsalesamount )
    )

[Ramachandran]

Based on your input, I made changes to the measure and then, I'm getting the result is below, 

Not showing all the row data
Running sales amount: =
VAR currentsalesamount = [SalesAmount]
RETURN
CALCULATE (
[SalesAmount],
FILTER ( ALL ( Sales ), [SalesAmount] <= currentsalesamount )
)

[rohit_singh]

Hi @Ramachandran ,

On your table, create calculated column called "Sales Rank" to rank sales in ascending order

Sales Rank =

RANKX(

RunningTotal,

RunningTotal[SalesAmount],

,

ASC,

Dense)

Next, create another calculated column to compute running total

Running Sales =

var _rank = RunningTotal[Sales Rank]

var _sum = CALCULATE(SUM(RunningTotal[SalesAmount]), FILTER(RunningTotal, RunningTotal[Sales Rank] <= _rank))

return

_sum

This should give you the desired output.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[Ramachandran]

Based on your inputs, I created Sales Rank and Running Sales measures.

Now, Running value is not showing correctly,

[rohit_singh]

Hi @Ramachandran , 

Please ensure these need to be calculated columns and not measures. 

[Ramachandran]

I created Ranking is calculated column and Running Sales is a measure.

[rohit_singh]

Hi @Ramachandran ,

Both need to be calculated columns 

[Ramachandran]

I have created both columns in calculated columns but is not working. 
for your reference, I have attached .pbix file link.
https://drive.google.com/file/d/1QdUPg6K2-7Jzk-sfbQ012GxROXaVVgol/view?usp=sharing

please check

[rohit_singh · Accepted solution]

Hi @Ramachandran ,

I've fixed it for you now. Please add the highlighted bit to your code and it will work

Ranking_ =

RANKX(

Sales,

CALCULATE(SUM('Sales'[SalesAmount]), ALLEXCEPT(Sales,Sales[ProductCategory])),

,

ASC,

Dense

)

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[rohit_singh]

Hi @Ramachandran ,

Could you please share the DAX code that you've used to create these columns?

[Ramachandran]

I used the code is below,

Ranking = RANKX(
ALL('Sales'[ProductCategory]),
[SalesAmount],,
ASC,
DENSe
)

Running sales =

var _rank = 'Sales'[Ranking]
var _sum = CALCULATE( Sum('Sales'[SalesAmount]),
FILTER('Sales',
'Sales'[Ranking] <= _rank)
)

return
_sum