cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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!

1 ACCEPTED SOLUTION
Frequent Visitor

Hi All,

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

Thank you

2 REPLIES 2
Frequent Visitor

Hi All,

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

Thank you

Community Support

Hi, @deepalig

Glad to hear you solved the problem yourself!

Congratulations

Best Regards

Yongkang Hua

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors