Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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.
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.)
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] ) )
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.
Solved! Go to 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] ) ) )
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] ) ) )
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.
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |