Hi
There are a few queries within one. I have attached an example work book. I am trying to understand how I can use pivots to create reports.
I have data for Budget and Actuals, by categories and by month (finance year running April - March)
I want to be able to Pivot to be able to compare Actual to Budget in two ways:
- new column in the Pivot that is Actual Value - Budget Value (so a currency value)
- another column that is % of Budget (So the difference above divided by Budget value expressed as %
You can see I have made an attempt by pulling value into the Values field twice, this gave me new columns that I could create a difference, but repeats and i cant remove the second or calculate the %
I also need to add a YTD column, which would be the sum of all months to the reporting
Another critical piece is I would need this to be Dynamic, so If I selected May, that it would be able to calculate the YTD field that is the sum of April and May (for Budget and Actuals), if I selected June, it would calc YTD as sum (April:Jun) for bud and actuals, etc.
Please help, this will save me so much time. I have posted in Power pivot as I am not sure if normal pivot can achieve this or if i need to move to power pivot.
Thank you
Hi Declan,
I moved this post to the Power Pivot course members group so that it gets priority over the open Power Pivot group.
You need to unpivot the actuals and budget values into separate columns. In the attached file I've done this for you using Power Query and then written the two measures for the variance and % of budget.
For the YTD calculations you need a date/calendar dimension table and you need to use time intelligence functions. Please complete the session on the date table; 3.09 and session 10 on time intelligence functions and have a go at doing that yourself. I'm here if you get stuck.
Mynda
