Forum Discussion

Ramachandran's avatar
Ramachandran
Icon for Helper III rankHelper III
4 years ago
Solved

Running value is not working as expected

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

Need Help
Show and Tell
Tips and Tricks
  • rohit_singh's avatar
    rohit_singh
    4 years ago

    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! ğŸ˜Š

11 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Ramachandran
      Icon for Helper III rankHelper III
      4 years ago

      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's avatar
        rohit_singh
        Icon for Solution Sage rankSolution Sage
        4 years ago

        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! ğŸ˜Š