TopN

[saintpatrick]

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))

[harshnathani]

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])

 

Please give kudos incase this is the solution and mark as 'Solved'

[saintpatrick]

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?

[harshnathani]

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. 

[saintpatrick · Accepted solution]

Hi @harshnathani 

This exactly what I want. Thanks.