Pivot table, unable to hide months with no value.

Josh_Godec123
Josh_Godec123 Member
in Beast Mode

I would like to list all the month actuals first, then month's budget numbers, etc.. (ie. Jan actuals, Feb, Actuals, March Actuals. then Jan Budget, Feb Budget, etc.) Unfortunately I do not see a way to do this without creating a separate column for each of the months. This solution however does not allow me to hide columns we do not have financials for. All recommendations are welcome. (I know I can manually add/hide months every month in Analyzer but was hoping to avoid that and leaves room for manual error)

Currently it lists actual, budget, variance for each month together. This is not how i want it.

image.png

Best Answer

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    Similar to what you're saying, you could use an ETL to restructure your data to something like:

    Type Index | Description | Month | Actual/Budget/Variance | Amount

    To do that, you could:

    • split your dataset using two filter tiles into actual and budget
    • Join them back together on Type/description/month
    • Use a formula tile to calculate variance. So you should now have something like:
      • Type Index | Description | Month | Actual | Budget | Variance
    • Dynamic Unpivot your data to stack the Actual/Budget/Variance on top of each other

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Answers

  • GrantSmith
    GrantSmith Coach

    Have you tried displaying your Actual / Budget / Variance and then the month in your pivot table so that the ordering is reversed?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Josh_Godec123
    Josh_Godec123 Member

    Yes, so that would be ideal if my data was set up that way.. I understand what you are saying. I have actual and budget in the data so this works perfectly, but unfortunately Variance is a beast mode calculation. Doing it this way is perfect for Actual and Budget, but then I cannot have a variance number.

    image.png
  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    Similar to what you're saying, you could use an ETL to restructure your data to something like:

    Type Index | Description | Month | Actual/Budget/Variance | Amount

    To do that, you could:

    • split your dataset using two filter tiles into actual and budget
    • Join them back together on Type/description/month
    • Use a formula tile to calculate variance. So you should now have something like:
      • Type Index | Description | Month | Actual | Budget | Variance
    • Dynamic Unpivot your data to stack the Actual/Budget/Variance on top of each other

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Categories