Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pelowski
Helper III
Helper III

DAX Modeling/Granularity Problem

In our model we have a traditional order and order detail fact table relationship. However, some order detail line items in the fact table are really two different products sold together. Unfortunately rather than storing the granularity of each product, they are grouped together into a separate "bundle product" and only one record is shown in the Order Detail table for the two items. So by nature, we have a granularity issue.

 

As an example I have created a very simplied data set to show this. This is a view of how the data is stored in our reporting model.

Data in Order Detail.jpg


We have a dimension table that includes the various ways we want to filter and aggregate these records namely by the individual product, by all the similar bundles grouped together, and by the core product with the bundle.

Product Dimensions.jpg


The simplified data model looks like this. (There is a corresponding Sales Order table as well which is a parent for the Sales Detail shown here.)

Simplified Data Model.jpg


Now, because there are essentially three different sales columns whose value is relevant depending upon what it is we're rolling up, the DAX gets tricky and must use the correct total column for the product dimension column being chosen. Thankfully we only have one "bundle" that is made up of two core products and we're never going to store the data this way again (or at least over my dead body) but I'm not sure of the DAX to use to get only the columns we want for the right dimensions. I know this measure isn't correct because it won't work at the summary level but this is what I've come up with thus far.

Bundle Sales =
IF (
    HASONEVALUE ( 'Products and Bundles'[Product and Bundle] ),
    SWITCH (
        VALUES ( 'Products and Bundles'[Product and Bundle] ),
        "Product A +Bundle", SUM ( [Split Total for A] ),
        "Product B +Bundle", SUM ( [Split Total for B] ),
        SUM ( Sales[Line Item Total] )
    ),
    SUM ( Sales[Line Item Total] )
)

And while the results can be correct at the product level, the HASONEVALUE is false at the total lev....

 

Does this make sense what we're doing? We're debating on whether or not we should simplify the calculation by splitting up the Order Detail records to be two different lines for each bundle record but then it doesn't really represent how the data is stored and I'm not sure what other changes (if any) that would cause us to have to make on the many dimension tables we have connected to Order Detail.

 

Thanks for any recommendations you can provide.

1 ACCEPTED SOLUTION

Walter and I refactored the solution and eliminated some confusion and redundancy in the code (the SWITCH() and IF() statements based on [Product and Bundle] values are essentially the same). Here's a cleaner version that has the same result: 

 

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 or sum the [Order Detail Sales Amount] for all other [Product and Bundle] values
        MAX ( 'Products and Bundles'[Product and Bundle] ) = "ProductA +Bundle", SUM ( Fct_OrderLines[Product A Bundle Sales Amount] ),
        MAX ( 'Products and Bundles'[Product and Bundle] ) = "ProductB +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 create a temp table with SUMMARIZE()
        VAR ___ProductBundleTable =
            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
                SWITCH (
                    TRUE (),
                    --Show the specific sum of sales for either Product X +Bundle or sum the [Order Detail Sales Amount] for all other [Product and Bundle] values
                    MAX ( 'Products and Bundles'[Product and Bundle] ) = "ProductA +Bundle", SUM ( Fct_OrderLines[Product A Bundle Sales Amount] ),
                    MAX ( 'Products and Bundles'[Product and Bundle] ) = "ProductB +Bundle", SUM ( Fct_OrderLines[Product B Bundle Sales Amount] ),
                    SUM ( 'Fct_OrderLines'[Order Detail Sales Amount] )
                )
            ) --Calculate the Row Total by SUMX() across the temp table above.
        VAR ___TOTALAMOUNT =
            SUMX ( ___ProductBundleTable, [Amount] )
        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] )
    )
)

View solution in original post

3 REPLIES 3
DirkFrazier
Advocate I
Advocate I

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

Walter and I refactored the solution and eliminated some confusion and redundancy in the code (the SWITCH() and IF() statements based on [Product and Bundle] values are essentially the same). Here's a cleaner version that has the same result: 

 

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 or sum the [Order Detail Sales Amount] for all other [Product and Bundle] values
        MAX ( 'Products and Bundles'[Product and Bundle] ) = "ProductA +Bundle", SUM ( Fct_OrderLines[Product A Bundle Sales Amount] ),
        MAX ( 'Products and Bundles'[Product and Bundle] ) = "ProductB +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 create a temp table with SUMMARIZE()
        VAR ___ProductBundleTable =
            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
                SWITCH (
                    TRUE (),
                    --Show the specific sum of sales for either Product X +Bundle or sum the [Order Detail Sales Amount] for all other [Product and Bundle] values
                    MAX ( 'Products and Bundles'[Product and Bundle] ) = "ProductA +Bundle", SUM ( Fct_OrderLines[Product A Bundle Sales Amount] ),
                    MAX ( 'Products and Bundles'[Product and Bundle] ) = "ProductB +Bundle", SUM ( Fct_OrderLines[Product B Bundle Sales Amount] ),
                    SUM ( 'Fct_OrderLines'[Order Detail Sales Amount] )
                )
            ) --Calculate the Row Total by SUMX() across the temp table above.
        VAR ___TOTALAMOUNT =
            SUMX ( ___ProductBundleTable, [Amount] )
        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] )
    )
)
v-joesh-msft
Solution Sage
Solution Sage

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.