Calculating Daily Totals based on helper sheets

[Anonymous]

Hello, 

I am very new to Power Bi but really enjoy it so far. I have a good level of knowledge with Excel pivot tables etc but I want to challenge myself and get to know this software more. 

I created this problem for myself and need some help in scoping how to approach different aspects of it. 

Let's suppose I own a chain of restarurants from which I obtain a report at the end of every day to say how many of different foods we sold. This table would include date, time, name of dish, etc. 

Since there are lets say.. 4 locations, each charging for the same food slightly differently, each site would be assigned under a "group 1, 2, 3 or 4" which would determine what they should be charging for food. 

I would then have another table to keep track of which site belongs to what group - this would simply be "Site 1 / Group 2" etc. 

I would then have a table of the different foods, along with their price for each tier. - "Food 1 / Tier 1: £2.00 / Tier: 2 £2.50" etc. 

So things I would like this to be able to do: Calculate daily totals £ for each locations, based on their Tier which determines their pricing strucutre. 

So overall, it's about using these three tables, one which provides the raw data for site/food made, 2nd table to be used to determine first the group to which the site belongs, 3rd table to then determine the price for each food, and then calcualate daily £ totals. 

Any help would be greatly appreciated. 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please have a try.

Create a column.

Column = CALCULATE(MAX('price'[Value]),FILTER('price','price'[Food]=EARLIER(Restaurant[type])))

If I misunderstand your meanings, please provide more than two days' data and expected output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

is there a way to share a project here so far? 

[AlexisOlson]

You can post a link to your file uploaded in the cloud using a service like Dropbox / Google Drive / OneDrive / SharePoint / etc.

[smpa01]

@Anonymous  please provide some sample data and expected output.

[Anonymous]

Hi, Let's see.. 

The below would be a simplified verison of the daily product made for each site, in the final version I would like to input a table with many more rows for each site. 

Restaurant 1	Coffee	20/11/2021
Restaurant 1	Cake	20/11/2021
Restaurant 2	Coffee	20/11/2021
Restaurant 2	Cake	20/11/2021
Restaurant 3	Coffee	20/11/2021
Restaurant 3	Cake	20/11/2021
Restaurant 4	Coffee	20/11/2021
Restaurant 4	Cake	20/11/2021

Group List

Restaurant 1	Group 1
Restuarant 2	Group 2
Restaurant 3	Group 3
Restaurant 4	Group 1

And the price list

Food	Group 1	Group 2	Group 3	Group 4
Coffee	£1	£1,20	£1,10	£1,30
Cake	£2	£2,20	£2,10	£2,30

So I would like to create some chart to show how much each site has made £ each day, based on the group they belong to which determines the price for each item.

I hope this makes sense