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 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?

 

 

Need Help

2 Replies

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