Forum Discussion

raj777karthik's avatar
raj777karthik
Microsoft Employee
3 years ago
Solved

TOPN dax function as variable

Need to show top 5 services in table visual.

 

Service NameCountTotal Count
Apple43346
Samsumg67346
mi80346
oppo84346
google72346

 

Measure written we not able to do sum the count of all Services,

Thread_ = VAR A=TOPN(5,SUMMARIZE(VALUES('Stack Tags'),'Stack Tags'[Service],'Stack Tags'[Thread__],"SUM_",DISTINCTCOUNT('Stack Tags'[Thread__])),'Stack Tags'[Thread__],DESC)
Return
CALCULATE(SUMX(A,SUM('Stack Tags'[Thread__])))
@
Table Column

14 Replies

  • raj777karthik's avatar
    raj777karthik
    Microsoft Employee
    3 years ago

    Hi,

    Thanks for this approach,but we need to do this in existing table considering relationship.If i create table function then only few slicer interacting and not all.

    • v-yadongf-msft's avatar
      v-yadongf-msft
      Community Support
      3 years ago

      Hi raj777karthik ,

       

      If you don't want to create a new table, pleaser try following DAX to sort [Thread_] column:

       

      Rank = RANKX('Stack Tags','Stack Tags'[Thread__],'Stack Tags'[Thread__],DESC)

       

       

      Calculate the sum of the top 5:

       

      Total count = CALCULATE(SUM('Stack Tags'[Thread__]),FILTER(ALL('Stack Tags'),'Stack Tags'[Rank]<=5))

       

       

      Add the [Rank] column to Filters to filter out the top5: 

       

      Best regards,

      Yadong Fang

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • raj777karthik's avatar
        raj777karthik
        Microsoft Employee
        3 years ago

        In this since my service oppo has 84 count the rank is taking all these for 1 and second service mi  has 80 count ,rank is taken as 85 .And for third its taking as rank 165.

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    3 years ago

    Hi raj777karthik ,

     

    This is my test table:

     

    Please try following DAX to create a new table:

    New Table = TOPN(5,SUMMARIZE('Stack Tags','Stack Tags'[Service],'Stack Tags'[Thread__]),'Stack Tags'[Thread__],DESC)

     

    Then create a measure to calculate total count:

    Total count = CALCULATE(SUMX('New Table',CALCULATE(SUM('New Table'[Thread__]))),ALL('New Table'))

     

    You will get result you want:

    If I misunderstand your demands, please feel free to contact us in time.

     

    Best regards,

    Yadong Fang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.