Combining Actuals and Forecast with Different Dimensions
Hi! I’m currently working on a table where I need to combine my Actuals and Forecast. For context, these are the dimensions available:
Actuals
Vendor
GL Account
Country
Currency
Month
Forecast
Vendor Name
GL Account
Country
Currency
Month
Project Details
I’m having an issue because when I combine them, the Project Details dimension is being applied to every Vendor in my Actuals. Is there a way that I can combine them without removing the Project Details? We still want to see it especially if we don’t have an actual vendor yet. Thank you in advance for your help!
Page 1 / 1
hi @Suzzy,
I suggest this way, we’ll keep original dimension sets for both Actual and Forecast metrics - no need to remove or add Project Details dimension, then the way we pivot the table will help you review both metrics in the most logical way, detailed steps as below:
Step 1: Put both original Actual and Forecast metrics into 1 combine table, remember to include Project Details dimension in this table as well
Step 2: Arrange and pivot the table, put metrics and Project Details in column section with Project Details under the metrics, and Vendor in row section - with this arrangement, we will have “Total” display under Actual metric to indicate that Actual metric has no Project Details dimension, see my suggested screenshot as below:
Hi @Thu Mai thank you so much for your suggestion!
Sorry but I mistakenly mention Table instead of Metrics. I need to combine both Actuals and Forecast to create a New Metric. I tried updating my formula to set the Actuals to one specific Project Detail, see my updated formula below:
It somehow works, so for the Actuals I will always see it in “Default” Project Detail. Let me know if you see any issue on this.
hey @Suzzy, best to bring them in a table, but I see that you need a metric to pull final Vendor - Project data, and seems like we don’t have actual data down to Project Details level right?
if yes then your formula is fine, but I think it’s best to name that item “All Projects” or “Total” instead of “Default”.
hey @Suzzy , actually we can make this formula a bit better,
For that “All Projects” item in Project Details list, the Forecast will also pull out the total amount of all projects, so that we can have a more logical output for the Forecast compared to the Actual
Hi @Thu Mai thanks for this idea! Yeah this works too so that I can combine them into one line. My only concern is that it might confuse the users, but anyways I’ll give it a try. Thank you!
