Dynamic Table in DAX for Bin

[viera00]

Hello everyone.

I've an interesting requirement that has become a challenge right now. It sounds simple and the data is only contained in three tables, but the I've been strugling to implement the dax formulas.

For the description I will use the AdventureWorksDW model, that is very popular. The tables used are, FactsInternetSales, DimProduct, DimProductSubcategory

Requirement: The user wants a report, with a column chart. In the X axis will be the DimProductSubcategory Name, the value will be the sum of the Internate Sales. But the user will have a slicer, and will select the DimProductSubcategoryNames in the slicer, and the column chart will "update" showing one column for each selected subcategory , and a column named "Others" with the summed amout of the rest of the categories.

This can be implemented "Static" with the Group feature of powerbi desktop. But as described, this requirement is "dynamic" based on the user input in the slicer.

Suggestions are welcome.

Regards,

GV

[SqlJason · Accepted solution]

Not sure I understand all the requirements, but let me give it a try.

1) Create a calculated table (should be a disconnected table) which will have an extra row for Other. This is the column which will be used in the chart.

ProdSubCat_List = UNION(VALUES(ProductSubcategory[Product Subcategory Name]), 
ROW("SubCategoryName", "Other"))

2) Create a measure

NewSalesMeasure = 
VAR SelectedSales =
    CALCULATE (
        [Sales Amount],
        INTERSECT (
            VALUES ( ProductSubcategory[Product Subcategory Name] ),
            VALUES ( ProdSubCat_List[Product Subcategory Name] )
        )
    )
VAR AllSelectedSales =
    CALCULATE (
        [Sales Amount],
        INTERSECT (
            VALUES ( ProductSubcategory[Product Subcategory Name] ),
            ALL ( ProdSubCat_List[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", AllSales - AllSelectedSales,
            SelectedSales
        ),
        AllSales
    )

 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] 

 This should work

Update - 3/1/2017 I blogged about this here - http://sqljason.com/2017/03/dynamic-grouping-in-power-bi-using-dax.html

I have also optimized the formula in the blog, in case anyone is interested

[MMBT]

Hi @SqlJason,

thanks for the awesome reply, I can use that right now! I was wondering how to use this, if I need to have a stacked Slicer with Category and Subcategory? Obvioulsy add both of these columns to the calculated table but I struggle to change the measure to work now 😄

Thanks, really appreciate it!

[viera00]

Dear @SqlJason ,

Thank you very much for this solution. This is really what I was looking for. 

There are several posts online looking for this solution. I will try to link it to this post and credit you for the code.

Initially, when I pasted the code, as was not able to make it work. The "Other" field was returnin "0", and that was because the variables "AllSelectedSales" and "AllSales" where returning the same. Only when I replaced the ALL filter in the AllSelectedSales variable , for a ALLSELECTED, is that it started to work. I do not indentify exactly why, but it is working. So the change was:

VAR AllSelectedSales =
    CALCULATE (
       [TotalSales],
        INTERSECT (
            VALUES(DimProductSubcategory[EnglishProductSubcategoryName]) ,
            ALLSELECTED(ProdSubCat_List[EnglishProductSubcategoryName])
        )
    )

Any clue on why this happened ?

Thanks again, and hope you continue sharing your knowledge.

Have a nice day,

Regards,

GV

[SqlJason]

You are welcome :), glad that it was what you wanted.

As for the change, I can't say for sure why that would be the case unless I see your model and what you are placing on the visuals. But if this new table (which is ProdSubCat_List) is not used anywhere else in the visuals or filters, they should return the same for ALL and ALLSELECTED. The 

VALUES(DimProductSubcategory[EnglishProductSubcategoryName])  

is what drives the selection, so it shouldn't matter ideally. 

[Sean]

Yes I couldn't get it to give me different results with ALL vs ALLSELECTED

But really useful Measure!

Already incorporated in my reports!

@Phil_Seamark, @KHorseman, @Anonymous, @Vvelarde