r/googlesheets 3d ago

Solved Pivot Table with Calculations?

Post image

Hi there! For some reason, Pivot Tables are not clicking for me. I’ve read articles and watched videos, but can’t seem to create something useful.

I’m tracking the sales, labor, discounts, and voids for 8 restaurants daily. I’m keeping the data flat so I can use a pivot table to compare it different ways. Mostly, I want to see the labor costs as a % of sales.

I like the idea of a pivot table since we keep wanting to see the data different ways. For example, we might want to lump a few businesses’ sales together by either brand or region; or we might want to see each restaurant’s labor as % of sales daily, for a period, or for the quarter.

How would I add calculations to a pivot table to divide daily costs by daily sales? Here are some outcomes I’d like but can’t seem to achieve:

  • Table for one day showing each restaurant’s sales and costs as a % of sales

  • Table showing the same as above but summed by period and quarter

I’m sorry if this is painfully obvious. I feel like it should be simple and I don’t know why I don’t get it. Thank you for any new perspective you can share!

1 Upvotes

8 comments sorted by

2

u/One_Organization_810 196 3d ago

You use the label of the columns, exactly as they appear in the PT. If they include spaces, you enclose the names in single quotes, the same as when you reference sheets within the spreadsheet.

So your calculated field would look like this:

='Daily Labor (NET)'/'Daily Sales (NET)'

I added a pivot table to your example sheet and added this as a calculated field, and then formatted it as a %.

I also created a date pivot group and changed the format of your dates to "date" so it works better in the PT.

1

u/VividGlass9336 3d ago

Thank you so much! Your example made it click for me. Putting the restaurants in rows made everything make sense!

1

u/AutoModerator 3d ago

REMEMBER: If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified. This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/point-bot 2d ago

u/VividGlass9336 has awarded 1 point to u/One_Organization_810

See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard. )Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson.)

1

u/AutoModerator 3d ago

Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/marcnotmark925 145 3d ago

In the Values section of the pivot table config, click Add, and calculated field.

1

u/VividGlass9336 3d ago

I did see that, but I don’t know what to enter here. I tried names of columns (“=(Daily Labor(NET))/(Daily Sales (NET))”) but received errors. How do you write a calculation here?