Assume that a tabular database shows Year wise, month wise. Account and Activity wise expenditure under difference Expenditure heads. While it is easy to drag columns inside a Pivot Table to analyse data, one may want to compute the Account wise and Activity wise Variance between May and August for all expense types except two.
If one attempts to write a calculated item formula within a Pivot Table, then variance rows will appear even for cases where Budget figures are equal to Actual figures i.e. where variance is equal to 0.
You may view the question, actual dataset, the failed Pivot Table attempt and final Power Query attempt in this workbook.
Perform a Variance Analysis within a Pivot Table
{ 16 Comments }