Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

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

Anonymous
6 years ago
Hi Anonymous

This exactly what I want. Thanks.

4 Replies

Anonymous
Not applicable
6 years ago
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'
- Anonymous
  Not applicable
  6 years ago
  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?
  - Anonymous
    Not applicable
    6 years ago
    Hi Anonymous
    
    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.

Related content