Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
rlansing
Resolver I
Resolver I

Trying to Group Brand Names

Hello,

I want to show the top 10 Brands in a table with all of the corresponding measures (Dollar Volume, Dollar Volume Year Ago, etc..) and then have an 11th row that is called "All Other" that sums up everything that is not a part of the Top 10. This inconjunction with Dollar Share and such will show me a great comparison of how much business the other brands do.

Okay so if I just use this measure I can apply a label to each Brand that shows if it is in the Top 10 or not:

Top 10 Brands = IF ( [$ Brand Rank] <= 10, "Top 10", "All Other" )

However, I really want to replace the Brand Name column in my table with this measure and actually show the Brand Name if the Brand Ranking is <= 10, otherwise make the Brand Name "All Other" and the rest of the brands will sum up.

 

I hope this makes sense. The problem I am having is changing out the "Brand" column for the "Top 10" text in the measure above.

 

Can anyone point me in the right direction?

 

 

2 REPLIES 2
greggyb
Resident Rockstar
Resident Rockstar

You could make a Calculated Column [Top10Name]:

// DAX
// Calculated Column
Top10Name =
IF(
    [$ Brand Rank] <= 10
    ,DimProduct[Brand Name]
    ,"All Other"
)

Now you can use this field as the axis/row label in a visual.

 

Unfortunately, there's no way to do this dynamically in PBI, so this top 10 will be updated only based on model refresh, and will not respect slicer selections (Calculated Columns are not evaluated at runtime).

There is no straightforward way, but depending on your requirements this workaround might work. 

 

For this demo purpose, lets say my model has a Brand table, a country table, and sales for Brand and country

t1.png

Follow the steps below:-

1) We need a disconnected Brand table which has an extra value of All Others, like shown below. In Power BI, we can use calculated tables to make that, something like

=UNION(values(Brand[Brand]), ROW("Brand", "All Others"))

For now, I just made a linked table in excel

t2.png

3) We will be usingthis disconnected table for our analysis. Take a look at the data model so far.

  t3.png

4) Make the following measures

 

TotalSales:=sum([Sales])    -- Regular sales measure

 

Rnk:=IF(HASONEVALUE(Brand[Brand]) && NOT(ISBLANK([TotalSales])),
RANKX(ALL(Brand[Brand]), [TotalSales]),
BLANK()
)  -- This measure will rank Brands by sales

 

--This measure will show sales for the disconnected Brands table

DisconnectedSales:=IF(HASONEVALUE(DisconnectedBrand[Brand]), CALCULATE([TotalSales], FILTER(Brand, Brand[Brand]=LOOKUPVALUE(Brand[Brand], Brand[Brand], VALUES(DisconnectedBrand[Brand])))))

 

--This measure will show Rank for the disconnected Brands table

DisconnectedRank:=IF(HASONEVALUE(DisconnectedBrand[Brand]), CALCULATE([Rnk], FILTER(Brand, Brand[Brand]=LOOKUPVALUE(Brand[Brand], Brand[Brand], VALUES(DisconnectedBrand[Brand])))))

 

--This measure will show Sales if Rank<=3, else it will show blank for rank>3. But if Brand Name is AllOthers, it sums up    

 --rank>3

test:=SUMX(VALUES(DisconnectedBrand[Brand]), IF(DisconnectedBrand[Brand]="All Other", SUMX(VALUES(Brand[Brand]), IF([Rnk]>3, [TotalSales])), IF([DisconnectedRank]<=3, [DisconnectedSales])))

 

5) This is how the sales and rank looks against the regular brand table

t4.png

 

6) This is how the Disconnected Brand table is looking - it shows the top 3 with All Other. 

t5.png

 

7) You can also see that it works with the slicer conditionst6.png

 

As I said initially, this is more of a workaround, so if your situation and may not perform that well with large sets of data. (I think I can tune these calculations further, but wasn't sure if it was even worth the effort if it doesn't meet  the requirements)

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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