October 10, 2018 at 3:04 pm #10925
I’m trying to put in place an actual versus budget analysis thanks to power pivot but I’m a bit stock at that stage, therefore I would need your precious help.
Attached is an example file.
-> in the tabs Actuals, BP18, BP19 : you can find the data
-> tab PIVOT : it’s actually the draft of the pivot I’ve got, it’s Under construction
-> tab data conversion target : it shows how i would like to convert the data that are in the tabs Actuals, BP18, BP19
-> tab TARGET : it is showing what is the end result I’m aiming to = a pivot in which I can see actual, MTD and YTD actual and BP amount depending on a slicer based on GL period
Saying that, I’m facing two problems :
-> I would like to convert the data which are in the tabs BP19 and BP18 in the format I’m showing in the tab Data conversion target. The aim is to get the GL period in one column and not in line as it is so far because I would like to have only one tab in which I will have the two different type of budgets : BP18 and BP18 (linked to scenario tables)
-> how can I get total QTD and YTD amount changing based on GL period (in a slicer) ? for example, when I select May-XX in the slicer, I would like to see :
– the MTD amount for actual, BP18, BP19 and the variances, meaning the amount for May
– the QTD amount for actual, BP18, BP19 and the variances, meaning the sum of April and May
– the YTD amount for actual, BP18, BP19 and the variances, meaning the sum from January to May
Thank you in advance for your help !
ie: if you want to modify something in the file in order to have the result I’d like, please do so 🙂October 10, 2018 at 3:15 pm #10926
- Started: 7
- Replies: 2517
- Total: 2524
When a file size exceeds 1024K, a couple of options are to create a zip file or to create a link to a copy on OneDrive (or similar service).
TomOctober 10, 2018 at 3:45 pm #10927
I do apologize, please find it in zip format.October 10, 2018 at 3:49 pm #10928
Since it didn’t work with the zip format, please find the link on my Icloud : https://www.icloud.com/iclouddrive/0OKNGari0QpfcWXQTJ8l2_CvA#Exemple_powerpivot.zip
You must be logged in to reply to this topic.