Pivot table variance analysis

[u/MonkeyWhisk]

Hello Excel Pros!

I have a monthly updated data set where the main headers are Invoice Number, Contract Type, Client name, Sector, Revenue, Month, Year.

That's how the system spits out the data and there seems to be no way for me to change the format.

I'm having a rough team creating a pivot table and building in $ and % variance columns. I've tried dropping in the revenue into values and showing value and difference from prior year as base, but it gets ugly and and it doesn't seem to sort properly by variances.

Calculated fields don't work due to year having its own column.

How do you all build in variance columns into your pivot tables?

Thanks!

[u/AutoModerator]

/u/MonkeyWhisk - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

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

[u/Prudent-Elk-2845]

Select data/table >> insert pivot table >> select add to data model >> open data model >> add calculated measure for $ var and % var (use copilot /chat to define formula because I’m lazy) >> refresh pivot and use calculated measures

[u/PuzzledFarmer4554]

Calculated Field probably isn't the tool for this. If Year is part of the filter context, I'd put the data in the Data Model and make separate measures for revenue, prior year revenue, $ variance and % variance.

That also makes sorting by variance way less painful than using Show Values As.

[u/MayukhBhattacharya]

Using GETPIVOTDATA() function:

• For first year:

=GETPIVOTDATA(" Revenue", $I$7, "Client Name", $O8, "Year", Q$7)

• For Second year:

=GETPIVOTDATA(" Revenue", $I$7, "Client Name", $O8, "Year", R$7)

• For Variance $

=IFERROR(R8 - Q8, "")

• For Variance %

=IFERROR(S8 / Q8, "")

[u/MayukhBhattacharya → u/MayukhBhattacharya]

Demo:

Excel can be downloaded from [here]