cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculating at Lower Grain Before Rollup

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.

• Step 1 calculation need to reflect the individual product code volume divided by the total volume sales within category:
• 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])

• Step 3 should then create the final number that I need
• 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%
1 ACCEPTED SOLUTION
Super User

Hi @jbloomfield ,

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Super User

Hi @jbloomfield ,

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements

#### Launching new user group features

Learn how to create your own user groups today!