DAX Modeling/Granularity Problem

Hi pelowski ,

This looks like a measure totals problem. The following posts may be helpful:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Best Regards,Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi pelowski 

There may be other ways (Walter and I work together and he said he found a way to do this in PowerQuery too!), but I found that we could hijack the filtered context of the slicer/filter selections and create a temporary table in DAX to contain the more than one filtered Product+Bundle value, and the calculated total sales amount for those values. Then we SUMX() across the temp table to do the calculation of the total row, when there is more than just a single slicer/filter selection. The following code came from inspirations by v-joesh-msft posted links to content authored by Greg_Deckler and of course the most awesome marcorusso with this great article: https://www.sqlbi.com/articles/displaying-filter-context-in-power-bi-tooltips/ which inspired me to grab the filter context and use it accordingly.

Sales =
IF (
    HASONEVALUE ( 'Products and Bundles'[Product and Bundle] ),
    --If only one [Product and Bundle] value has been selected in a slicer or filter, then
    SWITCH (
        TRUE (),
        --Show the specific sum of sales for either Product X +Bundle
        AND (
            MAX ( 'Products and Bundles'[Product and Bundle] ) = "ProductA +Bundle",
            ISFILTERED ( 'Products and Bundles'[Product and Bundle] )
        ), SUM ( Fct_OrderLines[Product A Bundle Sales Amount] ),
        AND (
            MAX ( 'Products and Bundles'[Product and Bundle] ) = "ProductB +Bundle",
            ISFILTERED ( 'Products and Bundles'[Product and Bundle] )
        ), SUM ( Fct_OrderLines[Product B Bundle Sales Amount] ),
        SUM ( 'Fct_OrderLines'[Order Detail Sales Amount] )
    ),
    IF (
        ISFILTERED ( 'Products and Bundles'[Product and Bundle] ),
        --If there are multiple [Product and Bundle] values selected in a slicer or filter, then 
        VAR ___ProductBundleTable =
            SUMMARIZE (
                --CREATE A Temp table with SUMMARIZE()
                'Products and Bundles',
                'Products and Bundles'[Product and Bundle],
                --Based on the 'Products and Bundles' table, for every [Product and Bundle] value in the slicer/filter, add Columns:
                "ProductBundle", FILTERS ( 'Products and Bundles'[Product and Bundle] ),
                --"ProductBundle" column contains the filtered [Product and Bundle] values
                "Amount", --"Amount" column contains the appropriate [Order Detail Sales Amount] based on the filtered [Product and Bundle] value
                IF (
                    MAX ( 'Products and Bundles'[Product and Bundle] ) = "ProductA +Bundle",
                    SUM ( 'Fct_OrderLines'[Product A Bundle Sales Amount] ),
                    IF (
                        MAX ( 'Products and Bundles'[Product and Bundle] ) = "ProductB + Bundle",
                        SUM ( 'Fct_OrderLines'[Product B Bundle Sales Amount] ),
                        SUM ( 'Fct_OrderLines'[Order Detail Sales Amount] )
                    )
                )
            )
        VAR ___TOTALAMOUNT =
            SUMX ( ___ProductBundleTable, [Amount] ) --Calculate the Row Total by SUMX() across the temp table above.
        RETURN
            ___TOTALAMOUNT,
        --Display the calculated Row Total when there are multiple [Product and Bundle] values in the slicer or filter.
        SUM ( 'Fct_OrderLines'[Order Detail Sales Amount] )
    )
)

The code isn't too bad to follow and the only issue I've found is if you try and make a GROUP in Power BI using the [Product and Bundle] values, and then use that GROUP as a slicer, the code above doesn't work because the filter context is no longer specific to the [Product and Bundle] field, but the GROUP value... Anyway, hope this helps.

Dirk