Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all - new to the Power BI platform and am having a calculation issue that I am hoping someone can help me out with. I have a report where the user can select one or more current weeks and a brand, and based on those selections we are doing a pricing comparison year over year.
In the below tables, the PBI Detail reflects the selection for a specific week and ice cream brand. Steps 1,2,3 are all calculated measures as they have to be dynamic and update based on those user selections. The Details section shows accurate numbers - the issue I am running into is when I am trying to roll everything up.
Volume Share =
VAR indvol = sumx('Sample Data','Sample Data'[Volume Sales])
VAR allvol = CALCULATE(sum('Sample Data'[Volume Sales]),ALLSELECTED('Sample Data'[Product Code]))
RETURN
(indvol / allvol)
Step 2 should reflect the everyday price * the volume share at the product code level
Price x Volume =
sumx('Sample Data','Sample Data'[Everyday Price])*([Volume Share])
Final Everyday Price =
(([Total Price 1YA] + [Price x Volume]) / [Total Price 1YA]) -1
The issue that I am running into is I need to show the info at the Category dimension only without Product Code. However when I remove product code everything is getting aggregated up so step 2 is coming out incorrectly (see PBI Rollup). What I really need is an aggregate of the Step 2 Product level values from the PBI Details (see Excel Rollup).
Looking for a recommendation on the best way to solve this? Basically I need that Step 2 calc initially done at a lower grain and then rolled up (if I am thinking about it correctly).
PBI Details | ||||||||
Week Ending | Category | Product Code | Volume Sales | Total Price 1YA | Everyday Price (in table) | Step 1 (Prod Volume Share to Cat) | Step 2 (Price x Volume) | Step 3 Everyday Price |
6/28/20 | MULTI SERVE | 2526702006573 | 8266.63 | 5.79 | -0.1328 | 0.3351 | -0.0445 | -0.7687% |
6/28/20 | MULTI SERVE | 2526702006603 | 4474.89 | 5.47 | -0.0161 | 0.1814 | -0.0029 | -0.0534% |
6/28/20 | MULTI SERVE | 2526702006633 | 976.54 | 5.39 | -0.2614 | 0.0396 | -0.0103 | -0.1920% |
6/28/20 | MULTI SERVE | 2526702006873 | 10952.62 | 5.78 | -0.1119 | 0.4440 | -0.0497 | -0.8590% |
6/28/20 | SINGLE SERVE | 2526702009783 | 1887.74 | 7.96 | -0.0985 | 0.1121 | -0.0110 | -0.1389% |
6/28/20 | SINGLE SERVE | 2526702009813 | 5905.85 | 8.16 | -0.1990 | 0.3509 | -0.0698 | -0.8557% |
6/28/20 | SINGLE SERVE | 2526702009843 | 7611.31 | 8.15 | -0.2451 | 0.4522 | -0.1108 | -1.3601% |
6/28/20 | SINGLE SERVE | 2526702009873 | 1428.02 | 7.86 | -0.0327 | 0.0848 | -0.0028 | -0.0353% |
PBI Rollup (Incorrect) | ||||||||
Week Ending | Category | Product Code | Volume Sales | Total Price 1YA | Everyday Price (in table) | Step 1 (Prod Volume Share to Cat) | Step 2 (Price x Volume) | Step 3 Everyday Price |
6/28/20 | MULTI SERVE | 24670.68 | 5.68 | -0.5222 | 1.0000 | -0.5222 | -9.1937% | |
6/29/20 | SINGLE SERVE | 16832.92 | 8.08 | -0.5753 | 1.0000 | -0.5753 | -7.1200% | |
Excel Rollup (Correct) | ||||||||
Week Ending | Category | Product Code | Volume Sales | Total Price 1YA | Everyday Price (in table) | Step 1 (Prod Volume Share to Cat) | Step 2 (blue or green cells in detail) | Step 3 Everyday Price |
6/28/20 | MULTI SERVE | 24670.68 | 5.68 | -0.5222 | 1.0000 | -0.1074 | -1.8916% | |
6/29/20 | SINGLE SERVE | 16832.92 | 8.08 | -0.5753 | 1.0000 | -0.1945 | -2.4068% |
Solved! Go to Solution.
Hi @Anonymous ,
My only question on this is the calculation of the Total Price 1YA since I don't have that information I cannot get the correct result for step 3 however your issue is with the calculation on step 2 you need to redo your calculation of the SUMX to the following:
Price x Volume =
var Category_Selection = SELECTEDVALUE('Sample Data'[Category])
VAR totalprod =
CALCULATE (
SUM ( 'Sample Data'[ Volume Sales ] ),
'Sample Data'[Category] = Category_Selection
)
var temp_table =
SUMMARIZE (
'Sample Data',
'Sample Data'[Week Ending],
'Sample Data'[ Volume Sales ],
'Sample Data'[Category],
'Sample Data'[Everyday Price (in table)],
"@VolumeShare", DIVIDE ( SUM ( 'Sample Data'[ Volume Sales ] ), totalprod )
)
return
SUMX(temp_table, 'Sample Data'[Everyday Price (in table)] * [@VolumeShare])
Has you can see the Price X Volumne is returning the correct value if you now use this calculation alongside on the step 3 should give the correct result.
Don't pay attention to the Total Price Y1A because has I refer not sure how you have calculate the value.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
My only question on this is the calculation of the Total Price 1YA since I don't have that information I cannot get the correct result for step 3 however your issue is with the calculation on step 2 you need to redo your calculation of the SUMX to the following:
Price x Volume =
var Category_Selection = SELECTEDVALUE('Sample Data'[Category])
VAR totalprod =
CALCULATE (
SUM ( 'Sample Data'[ Volume Sales ] ),
'Sample Data'[Category] = Category_Selection
)
var temp_table =
SUMMARIZE (
'Sample Data',
'Sample Data'[Week Ending],
'Sample Data'[ Volume Sales ],
'Sample Data'[Category],
'Sample Data'[Everyday Price (in table)],
"@VolumeShare", DIVIDE ( SUM ( 'Sample Data'[ Volume Sales ] ), totalprod )
)
return
SUMX(temp_table, 'Sample Data'[Everyday Price (in table)] * [@VolumeShare])
Has you can see the Price X Volumne is returning the correct value if you now use this calculation alongside on the step 3 should give the correct result.
Don't pay attention to the Total Price Y1A because has I refer not sure how you have calculate the value.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |