TopN with other Share at brand level. Other values is not giving correct value.

[deepalig]

Hi All,

I am trying to get top 10 brands with their share % and other should be give share % for rest of the brands.

I checked a few methods which helped me getting my top 10 share but other is giving 100 % which is not the right value.

DAX

1. I created a separate brand table with Other as new row as below.

TopBrands =

    UNION(

        VALUES('Table'[brand]),

        ROW("Brand","Other")

    )
          also created a column named Order using dax as 

          Order brands = if('Top Brands'[brand]="Other",2,1)

2. Created one to many relationship  between 'TopBrands' table and main table on brand
3. Created a rank measure         

Brand Ranking by share % =

 RANK(DENSE,ALL('Top Brands'),orderby('Top Brands'[Order brands],ASC,[Brand Share %],desc))

4.Brand Share % is calculated based on nested measure to give values based on different slicer selection,for example -YTD,MTD and on each selection the value should change

Brand Share % =

var Divisor = CALCULATE([NestedMeasure], ALLSELECTED('Top Brands'[brand]))

RETURN DIVIDE([NestedMeasure], Divisor, BLANK())

5. At last created 

Total with other =

if([Brand Ranking by share %] <= 10, [Brand Share %],

    if(HASONEVALUE('Top Brands'[brand]),

        if(values('Top Brands'[brand]) = "Other",

            sumX(filter(ALL('Top Brands'[brand]), [Brand Ranking by share %] > 10),  [Brand Share %])

        )

    )

This is giving me result as below screenshot where othe is coming up as 100% and is not correct.

Can someone help me in this?


Thank you in advance!

[deepalig · Accepted solution]

Hi All, 

Its resolved. Solved by modifying Brand Share% measure. Used All in place of Allselected fulfilled my requirement. 

Thank you 

[v-yohua-msft]

Hi, @deepalig 

Glad to hear you solved the problem yourself!  

Congratulations

Best Regards

Yongkang Hua