Hi
I have a DAX measure that is correct at the detail level but incorrect at the total level.
I created a new measure using SUMX and SUMAMRIZE to solve this – which gets me close but I still have one issue.
The issue I have is; the ‘Volume variance ($)’ measure below, is blank for rows where I do not have data in the current period - even though I have data in the previous period. Refer to cells marked as <No data?> below as an example of the issue.
In the example below, I do not have data for Product 3, in my current period. However, I do have data for Product 3 in the previous period – hence I am getting blank results for Product 3.
How can I change the Volume variance ($) measure to provide the ‘Revenue Variance’ for Product 3 in the 2 cells marked <No data?> below?
Note:
The DAX measure that works at a detail level is:
Volume variance ($) Line Item =
IF(SCP_Billing[Purchased 1 period] = TRUE(),
[Revenue Variance],
[Volume change (UoM)] * [Prior ASP]
)
The DAX measure that does not work is:
Volume variance ($) =
CALCULATE(
SUMX(
SUMMARIZE(SCP_Billing,
SCP_Billing[SoldToStateOffice],
SCP_Billing[SoldToAccountManagerName],
SCP_Billing[MaterialProductHierarchy1Name],
SCP_Billing[MaterialProductHierarchy2Name],
SCP_Billing[SoldToPartyName]
),
COALESCE(
[Volume variance ($) Line Item],
[Revenue Variance]
)
)
)
Here is an example of the data and the issue:
PH2 | Manager | Customer | Prior Net Sales | Net Sales | Revenue Variance | Volume change (UoM) | Prior ASP | Purchased 1 period | Volume variance ($) Line | Volume Variance ($) | Status |
Product 1 | Account Manger | Customer 1 | 376401 | 123339 | -253062 | -290640 | 0.88 | FALSE | -255763 | -255763 | Correct |
Product 2 | Account Manger | Customer 1 | 2480116 | 445928 | -2034189 | -1897236 | 1.08 | FALSE | -2049015 | -2049015 | Correct |
Product 3 | Account Manger | Customer 1 | 1981221 | -1981221 | -1722801 | 1.15 | TRUE | -1981221 | <No data?> | Get blank values when there is no Product 3 data in the current period | |
Product 3 | Account Manger | Customer 2 | 4329 | -4329 | -1604 | 2.7 | TRUE | -4329 | <No data?> | If there is no current or previous sales, it should just be equal to "Revenue Variance" | |
Product 4 | Account Manger | Customer 1 | 1849 | 1849 | 432 | TRUE | 1849 | 1849 | Correct |
Solved! Go to Solution.
Hi
I managed to solve this.
The root cause of the problem for me was, the "SUMMARIZE" portion of my calculation seemed to only include data related to my date slicer (which was from a date table with a relationship with my transaction data/fact table). So when I used any measure that used SAMEPERIODLASTYEAR, and there was data in the prior period, but not in the current period - I would not get prior period results (because of the date slicer & relationship).
So the solution was:
Once I removed the data table's relationship with the fact table, and manually derived all current/prior measures - my SUMX and SUMMARIZE measure worked as expected.
Here is the final measure.
Volume Variance ($) =
/* We need to use SUMX with SUMMARIZE in order to ensure the measure is calculated at a detail/line level and the results are sumamrized.
If we just report the measure below, it will be re-calclated at the total line level (context) and not reconcile with the sum of the indiviual lines */
SUMX(
ADDCOLUMNS(
SUMMARIZE(SCP_Billing,
SCP_Billing[SoldToStateOffice],
SCP_Billing[SoldToAccountManagerName],
SCP_Billing[MaterialProductHierarchy1Name],
SCP_Billing[MaterialProductHierarchy2Name],
SCP_Billing[SoldToPartyName]
),
"Volume Variance", /* Measure to calculate at the summarised level above */
IF(
SCP_Billing[Purchased 1 period] = TRUE(),
[Revenue Variance],
[Volume change (UoM)] * [Prior ASP]
)
),
[Volume Variance] /* Add the results of the measure calculated at the summarized level above i.e. ensure we do not re-calculate at the total line level (context) */
)
Hi
I managed to solve this.
The root cause of the problem for me was, the "SUMMARIZE" portion of my calculation seemed to only include data related to my date slicer (which was from a date table with a relationship with my transaction data/fact table). So when I used any measure that used SAMEPERIODLASTYEAR, and there was data in the prior period, but not in the current period - I would not get prior period results (because of the date slicer & relationship).
So the solution was:
Once I removed the data table's relationship with the fact table, and manually derived all current/prior measures - my SUMX and SUMMARIZE measure worked as expected.
Here is the final measure.
Volume Variance ($) =
/* We need to use SUMX with SUMMARIZE in order to ensure the measure is calculated at a detail/line level and the results are sumamrized.
If we just report the measure below, it will be re-calclated at the total line level (context) and not reconcile with the sum of the indiviual lines */
SUMX(
ADDCOLUMNS(
SUMMARIZE(SCP_Billing,
SCP_Billing[SoldToStateOffice],
SCP_Billing[SoldToAccountManagerName],
SCP_Billing[MaterialProductHierarchy1Name],
SCP_Billing[MaterialProductHierarchy2Name],
SCP_Billing[SoldToPartyName]
),
"Volume Variance", /* Measure to calculate at the summarised level above */
IF(
SCP_Billing[Purchased 1 period] = TRUE(),
[Revenue Variance],
[Volume change (UoM)] * [Prior ASP]
)
),
[Volume Variance] /* Add the results of the measure calculated at the summarized level above i.e. ensure we do not re-calculate at the total line level (context) */
)
Perhaps it will help if I simplify the calculation? Basically, if I use the calculaion below (which is the same as the original post - just simplified) - I will only get values for [Current Net Sales]. I will not get any data for [Prior Net Sales] although I can see there are 2 rows for [Prior Net Sales] - when I report [Prior Net Sales] in the table.
If this measure could return Prior net sales (if any) else current net sales - I could get my original measure to work.
Regards
@sfog does this help?
_measure =
SUMX (
ADDCOLUMNS (
SUMMARIZE (
SCP_Billing,
SCP_Billing[SoldToStateOffice],
SCP_Billing[SoldToAccountManagerName],
SCP_Billing[MaterialProductHierarchy1Name],
SCP_Billing[MaterialProductHierarchy2Name],
SCP_Billing[SoldToPartyName]
),
"test",
COALESCE (
IF (
SCP_Billing[Purchased 1 period] = TRUE (),
[Revenue Variance],
[Volume change (UoM)] * [Prior ASP]
),
[Revenue Variance]
)
),
[test]
)
Proud to be a Super User!
New Animated Dashboard: Sales Calendar
Hi @smpa01
No, it produces the same result i.e. I am not getting results for the prior period.
Thanks for looking into this.
Regards
@sfog I think it would help us to give the code for these measures
[Purchased 1 period],
[Revenue Variance],
[Volume change (UoM)],
[Prior ASP]
Also, the easiest way to help you would be to give us a sample PBix file but I assume this must be confidential data.
Here you go @m3tr01d
@sfog Whoa. "CALCULATE(SUMX(SUMMARIZE(..." I wish there was an easy way to point out all the context transitions going on here.
You are not showing the expected result at the total level - can you please add that to your sample data?
@lbendlin the data is very sensitive. I can skew the results but all I would be showing is an incorrect total of 5 detail rows - not sure it will add much value. The incorrect total is the common and expected result when using DAX i.e. calculating a % or a rate at a detail level works, but at the total level level it is re-calculate in the conext of the total line - nothing new or special here.
Hope that's okay and thanks for looking into this.
Regards