Dynamic Grouping in Power BI using DAX

Hex Map in Power BI
Hex Tile Grid Maps for Power BI
April 21, 2016
Visio Custom Visual (Preview) for Power BI – Quick Look
June 22, 2017
Show all

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.

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

7 Comments

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

  2. Vaibhav says:

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

  3. Abed says:

    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

    • SQLJason says:

      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.

      • Abed says:

        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

        • SQLJason says:

          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.

        • Abed says:

          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 ?

Leave a Reply

Your email address will not be published. Required fields are marked *