Pivot table, unable to hide months with no value.

Josh_Godec123 · 2023-09-14T17:50:29+00:00

There was an error rendering this rich post.

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.

Find more posts tagged with

Accepted answers

DavidChurchman

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

All comments

GrantSmith

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

Josh_Godec123

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.

DavidChurchman

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