Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Get top 3 names based on the highest amount values and remaining will be Others

Hi

 

Need to obtain Top 3 Names based on their amounts  and remaining will fall under others and values of others will be sum all amounts except top 3 values.

Note:  amount is a measure Value

Sample Data

No Name Amount

1        a       10

2        b       15

3        c       30

4        d       40

5        e      50

 

Expected result

 

Name     Amount

e                 50

d                 40

c                 30

others     25

General Comment

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous 

    Create measures

    rank = RANKX(ALLSELECTED('Table'),[amount],,DESC,Dense)

flag = IF([rank]<=SELECTEDVALUE('Table 2'[top n]),1,0)

Measure = IF(HASONEVALUE('Table'[Name]),IF([flag]=1,[amount],BLANK()),SUMX(FILTER(ALLSELECTED('Table'),[flag]=0),[amount]))

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Pbiuserr's avatar
      Pbiuserr
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      Hello, 
      Very nice approach. Is it possible to add more attributes? so besides top1-5 on value, there could be another value as well? and make 2 slicers so user decide which value he or she wants to see as 1-5? If yes, how to maintain it