Forum Discussion

rlansing's avatar
rlansing
Icon for Resolver I rankResolver I
10 years ago

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 sum...
Need Help
greggyb's avatar
greggyb
Icon for Resident Rockstar rankResident Rockstar
10 years ago

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).

SqlJason's avatar
SqlJason
Icon for Memorable Member rankMemorable Member
10 years ago

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

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

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

 

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

 

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

 

7) You can also see that it works with the slicer conditions

 

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)