Forum

Not sure how to gro...
 
Notifications
Clear all

Not sure how to group properly in Power Query

3 Posts
2 Users
0 Reactions
68 Views
(@cwilli1)
Posts: 3
Active Member
Topic starter
 

Hello,

The attached file mirrors a real world problem I am having with a budget file at work.   I am using Excel 2016 on my pc.  I do not understand how to prevent the budget sales number from multiplying by the number of actual sales transactions.   I realize I need to group the data somehow, but cannot figure out how to do it.  Please help.

Thanks,

Connie

 
Posted : 19/11/2020 3:14 pm
(@mynda)
Posts: 4762
Member Admin
 

Hi Connie,

You have two options:

1. Divide the total budget amount over the individual product items so the total of all items correctly adds up to your expected total budget amounts

2. Create a separate table containing the budget at the location level, if that's the lowest level you have the budget broken down by. Then use Power Pivot to summarise the data in a PivotTable. For this you need a dimension table to create the relationships between. If this means nothing to you, then option 1 is probably easier, otherwise you can learn Power Pivot here. In this course I cover this exact example.

Also note with option 2, that the lowest level you store the budget values is the lowest level you can analyse the actual vs budget at. e.g. if you only store budget at the location level of detail then you can't compare budget to actuals at the category, product or sales manager levels. 

I hope that makes sense, but let me know if you have further questions.

Mynda

 
Posted : 19/11/2020 6:14 pm
(@cwilli1)
Posts: 3
Active Member
Topic starter
 

Thanks so much for your quick response!

 
Posted : 01/12/2020 10:50 am
Share: