Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Top N

Hello All,

 

I want to show Top N(5) KPI based on variance whose fav_condition="2".  Can anyone tell me how to ahcieve this?

As I had passed the visual filter on fav_condition="2" and used then TOP 5 on KPI, its not showing properly.

I need to filter data by fav_condition="2" then need Top 5 kpi among that by variance 

 

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  Anonymous ,

     

    Create a measure as below:

    Measure = 
var _rank=RANKX(FILTER(ALL('Table'),'Table'[Fav_condition2]=2),CALCULATE(MAX('Table'[Variance])),,DESC)
Return
IF(MAX('Table'[Fav_condition2])=2&&_rank<=5,MAX('Table'[Variance]),BLANK())

    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!

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try measures like

     

    new var = calculate([variance], filter(Table, Table[fav_condition]="2"))

    Top 1 Rank = CALCULATE([new var ],TOPN(1,all(Table[KPI]),[new var],DESC),VALUES(Table[KPI]))

     

    replace 1 with N you want

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi  Anonymous ,

     

    Create a measure as below:

    Measure = 
var _rank=RANKX(FILTER(ALL('Table'),'Table'[Fav_condition2]=2),CALCULATE(MAX('Table'[Variance])),,DESC)
Return
IF(MAX('Table'[Fav_condition2])=2&&_rank<=5,MAX('Table'[Variance]),BLANK())

    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!