cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
viera00
Helper II
Helper II

Dynamic Table in DAX for Bin

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

German Viera
http://slidemodel.com/
1 ACCEPTED SOLUTION
SqlJason
Memorable Member
Memorable Member

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

 

temp.gif

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

View solution in original post

4 REPLIES 4
SqlJason
Memorable Member
Memorable Member

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

 

temp.gif

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

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

German Viera
http://slidemodel.com/
SqlJason
Memorable Member
Memorable Member

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
Community Champion
Community Champion

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

 

But really useful Measure! Smiley Happy

 

Already incorporated in my reports!

 

@Phil_Seamark, @KHorseman, @Anonymous, @Vvelarde

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors