Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Johan
Advocate II
Advocate II

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

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
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

 

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!

Thanks. That worked.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.