Calculating daily totals for products based on their grouping

[Anonymous]

Hello, 

https://drive.google.com/file/d/1nvznbPipaBNC8p6KxO4zra-Ra5Kw_n9c/view?usp=sharing

In this file you will see a list of transactions for each of the 4 restaurants. 

I am trying to calculate the daily totals for these sites based on the group they belong to, which changes the price for the same items. 

Any suggestions? 

TIA

[HotChilli]

merge queries is correct.  In database terminology it's a join.

What stage are you at now?  If you are stuck, post a link to your pbix (or send me a private message with it, if you don't want it made public)

[Anonymous]

Hi, 

It turns out there were some slight disrepencies between my raw data and my price list names.. caused the merge to behave weirdly, once I sorted that out it worked like magic. 

Thanks a lot! 

[HotChilli]

I'm reasonably sure the join will be on group AND product as per my previous message but it's possible that there are complications since I think you provided test data before and your real data has different names/values.

[Anonymous]

Thanks, 

Are you able to elaborate a little on what you mean by joining, I feel like there's a specific way I should be doing this.. currently using Merge Queries. 

TIA

[HotChilli · Accepted solution]

I understand what the rest. list table is for -> I'm saying that if you reshape the data in Power Query it won't be required anymore

"It's not possible to get this info into the "raw" table to begin with. " -> You can definitely do this in Power Query.

---

I'm thinking that you'll merge the Raw and rest. list tables first. (step 1)

Unpivot the  Group columns in Price List and then merge this table with step 1 on 2 columns (product and group) -> there might be a bit more to it but that's the strategy. 

[Anonymous]

Thanks a lot, 

So, I've managed to get your Step 1 sorted. I've merged the raw table along with the rest. list using the name as a common denominator so that's worked great. 

The problem I have when I try to do the similar but with the price list instead. 

I go to merge queries, and use "group" as a common denominator, and it loads fine, but when I go to show just the price, it shows just price in that column but also multiples rows of my tables.... 

Any help on how could get the prices into my "raw" list? 

TIA

[Anonymous]

Thanks for the suggestions.. especially on chaning the table name 😄 

Well the idea is that the Restaurant List table provides info on which group the individual restaurants belong to, which determines all the prices. 

It's not possible to get this info into the "raw" table to begin with. 

Do you have a suggestion on an alternative? Will your above suggestion work considering? 

[HotChilli]

I think I would use Power Query to bring the price into the Raw table (should change this to a more meaningful name) .  The rest. list table isn't really doing anything in the model (maybe with multiple restaurants it would be more useful)

This will simplify the model and make any DAX measures easier to write.