Forum Discussion

Johan's avatar
Johan
Icon for Advocate II rankAdvocate II
9 years ago
Solved

Accumulated value based on ranking DAX

Hi,

 

I'm looking for a dax formula to show the value accumulated based on it's ranking

 

Prod A = 3 (euro, dollar, kg, ..)

Prod B = 1

Prod C = 7

 

Ranking of products:

Prod A = rank 2

Prod B = rank 3

Proc C = rank 1

 

Now I want to present a graph:

X axis = rank 1, rank 2, rank 3

Y axis = value 7, 10, 11

 

I found the dax for the rank: RANKX(all(table[prod]);[value]).

 

Thanks for your support.

Johan

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Hi Johan,

     

    Greg_Deckler is right.  Perhaps try adding the following calculated columns to your table

     

    Rank = CALCULATE(COUNTROWS('Table1'),ALL('Table1'),'Table1'[Value] > EARLIER('Table1'[Value]))+1
    Cumulative = CALCULATE(SUM('Table1'[Value]),ALL('Table1'),'Table1'[Rank]<= EARLIER('Table1'[Rank]))

    You can then plot these two columns on the axis of a scatter as follows

     

     

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Perhaps a cumulative measure that does a SUM of any lower RANK?

    • Johan's avatar
      Johan
      Icon for Advocate II rankAdvocate II
      9 years ago

      Thanks for you comment, would you have an example of how to do that?

       

       

      • Phil_Seamark's avatar
        Phil_Seamark
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi Johan,

         

        Greg_Deckler is right.  Perhaps try adding the following calculated columns to your table

         

        Rank = CALCULATE(COUNTROWS('Table1'),ALL('Table1'),'Table1'[Value] > EARLIER('Table1'[Value]))+1
        Cumulative = CALCULATE(SUM('Table1'[Value]),ALL('Table1'),'Table1'[Rank]<= EARLIER('Table1'[Rank]))

        You can then plot these two columns on the axis of a scatter as follows