cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Johan Helper III
Helper III

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft Phil_Seamark
Microsoft

Re: Accumulated value based on ranking DAX

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

 

rank v cumulative.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Super User IV
Super User IV

Re: Accumulated value based on ranking DAX

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


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Johan Helper III
Helper III

Re: Accumulated value based on ranking DAX

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

 

 

Microsoft Phil_Seamark
Microsoft

Re: Accumulated value based on ranking DAX

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

 

rank v cumulative.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Johan Helper III
Helper III

Re: Accumulated value based on ranking DAX

Thanks. That worked.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors