cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jbloomfield
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 EndingCategoryProduct 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/20MULTI SERVE25267020065738266.635.79-0.13280.3351-0.0445-0.7687%
6/28/20MULTI SERVE25267020066034474.895.47-0.01610.1814-0.0029-0.0534%
6/28/20MULTI SERVE2526702006633976.545.39-0.26140.0396-0.0103-0.1920%
6/28/20MULTI SERVE252670200687310952.625.78-0.11190.4440-0.0497-0.8590%
6/28/20SINGLE SERVE25267020097831887.747.96-0.09850.1121-0.0110-0.1389%
6/28/20SINGLE SERVE25267020098135905.858.16-0.19900.3509-0.0698-0.8557%
6/28/20SINGLE SERVE25267020098437611.318.15-0.24510.4522-0.1108-1.3601%
6/28/20SINGLE SERVE25267020098731428.027.86-0.03270.0848-0.0028-0.0353%
         
PBI Rollup (Incorrect)       
Week EndingCategoryProduct CodeVolume SalesTotal Price 1YAEveryday Price (in table)Step 1 (Prod Volume Share to Cat)Step 2 (Price x Volume)Step 3 Everyday Price
6/28/20MULTI SERVE 24670.685.68-0.52221.0000-0.5222-9.1937%
6/29/20SINGLE SERVE 16832.928.08-0.57531.0000-0.5753-7.1200%
         
Excel Rollup (Correct)       
Week EndingCategoryProduct CodeVolume SalesTotal Price 1YAEveryday Price (in table)Step 1 (Prod Volume Share to Cat)Step 2 (blue or green cells in detail)Step 3 Everyday Price
6/28/20MULTI SERVE 24670.685.68-0.52221.0000-0.1074-1.8916%
6/29/20SINGLE SERVE 16832.928.08-0.5753                       1.0000-0.1945-2.4068%
1 ACCEPTED SOLUTION
MFelix
Super User
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])

 

MFelix_1-1627645844606.png

 

 

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



View solution in original post

1 REPLY 1
MFelix
Super User
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])

 

MFelix_1-1627645844606.png

 

 

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



View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.