Perform a Variance Analysis within a Pivot Table

{ 16 Comments }

By Ashish Mathur · · , , · Leave a comment

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.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • Very nice work, now i will try this out. My actual data is very large & many fields, hopefully this tools is powerful. I really appreciate your help.

    Reply

  • Ok, i was able to get the first step, but i am not able to create the Custom step that show as this

    Custom1 = Table.Pivot(Unpivot, List.Distinct(Unpivot[Edition]), “Edition”, “Value”, List.Sum)

    also for some reason, it says your file was created with a new version of power query, but i just downloaded mine. There is no update…

    Reply

      • Hi- it worked this time! Unfourtunatly I’m back to the drawing board, in creating a full solution. When this depivots the data, i loose the ability to preform calutated fields in a specific items.

        For example, I need to take the Exp.1 QT1 / PM1.1, and have this in my pivot per row.

        It looks like i might be able to use the power query tool for something like this, i just need to play around with it.

        Thanks Again, you have been a huge help.

        Reply

        • Hi,

          You are welcome. Please let me know what all you want to see in the Pivot Analysis so that I can design a solution accordingly. Just as you had shown the Desired Pivot Table worksheet with Variance, please show other such parameters as well so that I can think of how the data should structured.

          Reply

  • I did not reliaze change the data would have to be part of the solution, there was not even thinkin this could be a problem.

    Here in this link i have a new file that has some calculated fields with formulas in them.

    http://sdrv.ms/1dR4qiJ

    Reply

    • Hi,

      You may refer to my solution in the Ashish’s solution worksheet of this workbook.

      This is what I did

      1. Click on the filter drop down in cell A4 of the “Desired Solution” worksheet and select Activity in the Select Field Drop down
      2. Then Value Filter > Greater. Select exp/rev 1 is greater than 0
      3. Click on OK

      Hope this helps.

      Reply

      • I will have differences that are less then zero & this solution takes very long when working with actual data when you make any changes. I really liked the power query solution, I just need to figure out how to fully implent the idea.

        You have been a great help, thanks.

        Reply

        • Hi,

          I have been working on this and though I cannot get the exact structure that you want, I have been able to come up with two alternative structures which get the same results as yours. My solution uses a combination of Power Query and PowerPivot (DAX formulas).

          Here is the workbook with both alternative structures. If you are agreeable to any of these alternative structures (tell me which one), then I can proceed to compute the other values.

          Let me know.

          Reply

          • Ashish,
            I really appreciate all your effort, but I’m really tied to the structure. Its been used for a long while & is done manually. I could do with out the PQ formula, but i cant do without being able to calulate the other Rev/Exp….
            I know you have invested sometime helping me & i can’t thank you enough.

  • i viewed the Variance analysis in a Pivot Table.xlsx workbook and i could manage to repeat the same again based on your comments there. i mean unpivoting. however i failed to do the second step which was ” 2. The Edition column in the Data sheet has been converted into as many columns as there are months” this second option. i could not figure out how it is done. exactly like Justin referred, step one can be done and step two is not doable. perhaps i am missing something. i also unhidden the worksheet “Intermediary table-Power Query” and then loaded the data to see how you did it and by the time i load it i get the following error “Expression.Error: The import Table.Pivot matches no exports (did you miss a module reference?). Details: null” also when i click on the detail of code i get the below code

    let
    Source = Excel.CurrentWorkbook(){[Name=”Table4″]}[Content],
    Unpivot = Table.Unpivot(Source,{” PM1″, ” PM1.1″, ” PM1.2″, ” PM1.3″, ” PM1.4″, ” Expenses 1″, ” Exp.1 QT1″, ” Exp.1 QT2″, ” Exp.1 QT3″, ” Exp.1 QT4″},”Attribute”,”Value”),
    Custom1 = Table.Pivot(Unpivot, List.Distinct(Unpivot[Edition]), “Edition”, “Value”, List.Sum),
    GroupedRows = Table.Group(Custom1, {“Account”, “Activity”, “Year”, “Attribute”}, {{“May”, each List.Sum([May]), type none}, {“June”, each List.Sum([June]), type none}, {“July”, each List.Sum([July]), type none}, {“August”, each List.Sum([August]), type number}})
    in
    GroupedRows

    i just want to repeat when you did exaxtly in the workbook Variance analysis in a Pivot Table.xlsx but i am failing to do the second option. i would appreciate if you could help indentifying the problem.

    by the way, i am have the latest power query downloaded and installed today from microsoft website.

    Reply

    • Hi,

      I am assuming that you downloaded the Power Query add-in from this link. if yes, then this version does not include the Table.Pivot function. This function was introduced in the October refresh of the Power Pivot which you can download form here.

      Hope this helps.

      Reply

  • Hi Ashish,

    Many thanks. I installed the latest version and it worked this time.

    i was curious to know that since this is a very powerful tool, the code below is automatically created when i used unpivot. however i could not perfect the second step without putting the code there. my question is that did you type the code for the second step or you could do it with the existing interface without have to type the code. for the second step i tried with “Insert Custom Column, but i guess it is not possible to do it without having the knowledge of the coding in this power query.

    grateful if you could shed some light on this issue.

    thanks.

    let
    Source = Excel.CurrentWorkbook(){[Name=”Table4″]}[Content],
    Unpivot = Table.Unpivot(Source,{” PM1″, ” PM1.1″, ” PM1.2″, ” PM1.3″, ” PM1.4″, ” Expenses 1″, ” Exp.1 QT1″, ” Exp.1 QT2″, ” Exp.1 QT3″, ” Exp.1 QT4″},”Attribute”,”Value”),

    second code
    Custom1 = Table.Pivot(Unpivot, List.Distinct(Unpivot[Edition]), “Edition”, “Value”, List.Sum),
    GroupedRows = Table.Group(Custom1, {“Account”, “Activity”, “Year”, “Attribute”}, {{“May”, each List.Sum([May]), type none}, {“June”, each List.Sum([June]), type none}, {“July”, each List.Sum([July]), type none}, {“August”, each List.Sum([August]), type number}})
    in
    GroupedRows

    Reply