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
saintpatrick
New Member

TopN

Hi,

I intend to achieve "Table 2" outcome. However, my DAX isn't working as expected. Table 2 was achieved using the TopN function from the filter pane. 

 

How do I achieve "Table 2" using DAX? 

Here is my DAX command for "Table 1"

Top 2 = CALCULATE(SUM(Sheet4[value]), TOPN(2, Sheet4,Sheet4[Province], DESC))

TopN.PNG

1 ACCEPTED SOLUTION

Hi @harshnathani 

 

This exactly what I want. Thanks.

View solution in original post

4 REPLIES 4
harshnathani
Community Champion
Community Champion

1. Create a measure called 'Sum Values' to SUM the Values

Sum Values = SUM(Test[Values])

 

2. Create a measure Ranking to rank the  for the SUM Values

Ranking = RANKX(ALL(Test),[Sum Values],,DESC)
 
3. Create measure Final Sum Value to give you Top 2 Values
Final Sum Value = IF([Ranking]<=2,[Sum Values])
 
 
 
 test.PNG
 
Please give kudos incase this is the solution and mark as 'Solved'

Hi harshnathani

 

Thanks for your response. It does work. However, I've got 2 questions for you; 

 

1. Total isn't correct. It is showing the total for everything and not the top 2. 

2. Is it possible to achieve this using TOPN?

Hi @saintpatrick 

 

Please use this measure.

 

1. Final Sum Value = IF([Ranking]<=2,SUMX(TOPN(2,Test,[Sum Values],DESC),Test[Values]))
 
 
2. TOPN returns table. Refer the below link https://dax.guide/topn/.
 
Incase you want to create a Table, you can click on Create New TABLE and use the below code:
 
Table =
TOPN(
2,
SUMMARIZE(Test,Test[Name],"Values",[Sum Values]),
[Sum Values],
DESC)
 
Do let me know if this solved your problem. 
 
 
 

Hi @harshnathani 

 

This exactly what I want. Thanks.

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.

Top Solution Authors