Dynamic Grouping in Power BI using DAX

It has been quite a while since I posted something and was already thinking of dusting up my tools. That was when I was going through the Power BI Community forums, and found an interesting question –

Requirement: The user wants a report with a column chart. The X axis will have Subcategory Name and the value will be the sum of Internet Sales. Along with this chart, the user will have a slicer where they can select the Subcategory Names. The column chart should “update” showing one column for each selected subcategory, and another column named “Others” with the summed amount of the rest of the unselected categories.

Basically, they wanted a dynamic group called “Others” and the members within this group should change based on what is selected on the slicer.

This would be a good time to show a visual representation of what the requirement means.

You can see that there is one individual (green) column for every selected Subcategory and also one (orange/red) column called “Other” which has the summed up value for the rest of the unselected categories.

For solving this, follow the steps below:-

1) The “Other” member is not available in any existing column. So we will have to create a new table having a column for all the subcategories, as well as an additional member for Others. For this, I made a new calculated table in Power BI using the formula below

ProdSubCat_List =UNION (— get the existing values of subcategory name VALUES ( ProductSubcategory[Product Subcategory Name] ),— add the other member ROW (“SubCategoryName”, “Other”))

The Subcategory column from this table has to be used in the charts, since this is the only column which has the “Other” member. At the same time, this table is a disconnected table (which means that there is no relationship between this table and the rest of the fact/dimension tables), so we will not get any proper values if we just use the Sales measure with this column in a column chart. For that, we will have to create a custom measure.

Note that we are making use of 3 variables – SelectedSales, UnSelectedSales and AllSales to handle the 3 conditions that can arise.

SelectedSales will match the member values in the our calculated table (ProdSubCat_List) with the Subcategory names in the original Subcategory table and get their corresponding Sales Amount.

UnSelectedSales will get the Sales Amount for all the unselected Subcategory names, and we make use of the EXCEPT function for this.

AllSales is the total Sales Amount for all the Subcategories, and is used for showing the grand total.

3) Create a column chart with ProdSubCat_List[Product Subcategory Name] on axis and NewSalesMeasure on values. Put a slicer which has ProductSubcategory[Product Subcategory Name]. Now you can see the required end result.

the others for me seems to always show the total sales regardless of what is selected or not selected .
The only difference i can see if that i need to place SUM([Sales Amount]) not [Sales Amount] directly

Can you ensure that all the steps have been followed, or did you follow any variation of your own? If you write down your measures and give a basic understanding of your model, I can try to replicate and take a look.

1) For this technique to work, you need to create a lookup table for Card Name
CardName_Lookup =VALUES(‘Requête1′[CardName])

2) Create a relationship from ‘Requête1′[CardName] and CardName_Lookup[CardName]

3) Replace ‘Requête1′[CardName] with CardName_Lookup[CardName] in all the measures

Let me know if that works. Basically, the filtering will not work if you are not using a Lookup table.
In my case, ProductSubcategory is the lookup table, so double check the formulas also. Also make sure on what column you are placing on the slicer.

I have one more question though, in the Bar chart i want to show the top 5.
As in the top 4 and the 5th to be the Others, currently if i do not add a slicer it will just show the top 5 and not show the Others.

Thanks for the great post. I’m not sure if I am going mad…. in a similar situation I have tried to create a new dynamic table based upon (filtered by) the slicer selections of a disconnected table. I am attempting this as a workaround for the Power BI limitation of not being able to link a single slicer to multiple report tabs. My plan was to connect the resultant calculated table to the fact table for filtering purposes.

I am certainly able to “harvest” multiple selections in a Card visual using CONCATENATEX and ALLSELECTED but I can’t get a calculated table to behave the same way..

Have you successfully done something similar to create groups in scatter charts? I am trying to create a scatter chart with 2 groups only:
1) Area Selection
2) Other (all other areas within parent)

But it keeps failing, i was wondering if its because i am not calculating a value, i purely want to create the group to use as the legend, so i can then control the colouring (selection always the same colour regardless of what is selected, other different colour regardless of what areas are within other)