Forum Discussion

Ethanhunt123's avatar
Ethanhunt123
Icon for Helper IV rankHelper IV
6 years ago
Solved

Top N for only specific category (Avoid blanks)

I have a table that has 2 columns, Test_Name , Test_Value Test_Name Test_Value ABC 10 SDF 12 ERT 14 ASD     I have to find the top 1 Test_name based on the Test_value    M...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi  Ethanhunt123 ,

     

    Create a measure as below:

    Measure = 
CALCULATE(MAX('Table'[Test_Name]),TOPN(1,FILTER(ALL('Table'),'Table'[Test_Value]<>BLANK()),CALCULATE(MAX('Table'[Test_Value])),DESC))

    And you will see:

    For the related .pbix file,pls see attached.

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

     

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
6 years ago

Ethanhunt123 

 

Use RANKX instead of TOPN

(The name of the table in my exampe is 'TopN')

 

 

Rank Value = RANKX(ALL('TopN'), [Sum of Value],,DESC)

 

 

 

and if you want a measure for the top name only:

 

 

Top Name for Card Visual = CALCULATE(MAX('TopN'[Test_Name]), FILTER('TopN', [Rank Value] = 1))