Dynamic Grouping in Power BI using DAX

March 1, 2017

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.

1 Requirements

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.

2) The next step is to make a measure which will display the values

NewSalesMeasure =
VAR SelectedSales =
    CALCULATE (
        [Sales Amount],
        INTERSECT (
            VALUES ( ProductSubcategory[Product Subcategory Name] ),
            VALUES ( ProdSubCat_List[Product Subcategory Name] )
        )
    )
VAR UnSelectedSales =
    CALCULATE (
        [Sales Amount],
        EXCEPT (
            ALL ( ProductSubcategory[Product Subcategory Name] ),
            VALUES ( ProductSubcategory[Product Subcategory Name] )
        )
    )
VAR AllSales =
    CALCULATE (
        [Sales Amount],
        ALL ( ‘ProductSubcategory'[Product Subcategory Name] )
    )
RETURN
    IF (
        HASONEVALUE ( ProdSubCat_List[Product Subcategory Name] ),
        SWITCH (
            VALUES ( ProdSubCat_List[Product Subcategory Name] ),
            “Other”, UnSelectedSales,
            SelectedSales
        ),
        AllSales
    )

 

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.

2 End Result

Posted by SQLJason

12 comments

[…] Jason Thomas shows how to include an “All Others” member in a Power BI visual: […]

Is there a way to have cross filtering between the column chart and the table visual, where the table visual is based on the ProductSubcategory table(since I need to show breakdown on Product level.)

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.

I have a single table in the report , columns of interest are [CardName] & [LineTotal]

I created a new table with the following code :
CardName_List =
UNION(
VALUES(‘Requête1′[CardName]),
ROW(“CardName”, “Others”)
)

And added a measure to the table as the following :

NewLineMeasure =
VAR SelectedLines =
CALCULATE (
SUM(‘Requête1′[LineTotal]),
INTERSECT (
VALUES ( ‘Requête1′[CardName] ),
VALUES ( CardName_List[CardName] )
)
)
VAR UnSelectedLines =
CALCULATE (
SUM(‘Requête1′[LineTotal]),
EXCEPT (
ALL ( ‘Requête1′[CardName] ),
VALUES ( CardName_List[CardName] )
)
)
VAR AllLines =
CALCULATE (
SUM(‘Requête1′[LineTotal]),
ALL ( ‘Requête1′[CardName] )
)
RETURN
IF
(
HASONEVALUE ( CardName_List[CardName] ),
SWITCH
(
VALUES ( CardName_List[CardName] ),
“Others”,
UnSelectedLines,
SelectedLines
),
AllLines
)

In the page i placed a column chart and set the following config :
Axis: CardName_List.[CardName]
Value: CardName_List.[NewLineMeasure]

It is showing Others holding Sum ([LineTotal]) for everything and then the other elements with their own aggregation

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 found my mistake , in the :
VAR UnSelectedLines =
CALCULATE (
SUM(‘Requête1′[LineTotal]),
EXCEPT (
ALL ( ‘Requête1′[CardName] ),
VALUES ( CardName_List[CardName] )
)

It should be:
VAR UnSelectedLines =
CALCULATE (
SUM(‘Requête1′[LineTotal]),
EXCEPT (
ALL ( ‘Requête1′[CardName] ),
VALUES ( ‘Requête1′[CardName] )
)

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.

Any thoughts how to adjust to do this on the chart ?

Hi Jason,

Thank you for this post! Worked fine for me. But trying to go beyond this, is there a way to make slicers that shows automatically sellections for top 5, 7, and 10 SubProducts based on the same sales?

Regards!

Hi Jason,

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

Thanks in advance.

Hi Jason,

I think I may have found the answer in the following thread: http://community.powerbi.com/t5/Desktop/Calculated-Table-filtered-based-on-Slicer-selection/m-p/160250#M69640 as posted by http://community.powerbi.com/t5/user/viewprofilepage/user-id/6970

“The calculated table is preprocessed BEFORE use slicer visual to filter data. So we are not able to use the checked slicer value in the calculated table….”

Regards.

Steph Shadwell

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)

Hey,

Can this be customized for a dynamic Drill through filtered value?
i tried AllSelected

however that returns the complete data set, any pointers would help.

Thanks

Leave a Reply