Calculating Variance Between Two Tables in a Data Model

[bigburge10]

Hello,

I have two tables in Excel 2013: Actual & Forecast. Both tables have the following similar columns: Period, Account, Amount. I've included them in a Pivot Table Data Model and would like to calculate a variance between Actual[Amount] and Forecast[Amount]. I'm unable to utilize the Calculated Field/ Item since those aren't available when using a Data Model.

Both tables are related to 2 other tables: Period & Account--both of these tables provide additional information for these fields (Account Hierarchy, Period Intelligence, etc).

How can I perform such a calculation? It seems like such a simple thing, yet I can't seem to figure it out!

Thanks in advance, and please let me know if any additional information is needed.

 

[scottsen]

Generally, you are just going to write 3 calculated fields:

Total Actual := SUM(Actual[Amount])
Total Forecast := SUM(Forecast[Amount])
Variance := [Actual] - [Forecast]

 

[bigburge10]

Thanks for replying. Can you provide a little color around this? Where should I include these calculated fields? I assume you aren't referring to the actual Calculated Field option under the Fields, Items, and Sets area.

Are these getting entered to a new table?

 

[scottsen]

So... you are using the "Power Pivot" addin, yes? https://support.office.com/en-in/ar...er-Pivot-d3cc1495-b4e5-48e7-ba98-163022a71198

 

[bigburge10]

I'm not 100% sure of that. I do not have the Power Pivot menu on my bar. However, I'm using Excel 2013, and I believe it's up-to-date. I've tried to enable this add-in, however it doesn't appear in the add-in screen. So, I think (and I've read this) that Power Pivot is included in Excel 2013 by default. Do you know if that is true?

 

[scottsen]

As shocking as this may sound... the answer is actually "complicated". While Power Pivot used to be a free addin, in 2013 it is only available in certain SKUs of Office, which more or less boils down to Professional Plus (which is only available via Office365 or volume license) and stand-alone Excel... cuz Microsoft is literally insane.

So, if you are using normal excel, outside the Power Pivot world... I recommend hopping over to the "main" forum, where they will be way more qualified to answer your question.

 

[bigburge10]

Thanks for your help. I continued to search on this topic online and did see plenty of mentions of this. I'm pretty sure my company has a volume license, but maybe we purchased a cheaper version that still doesn't have this feature. Go figure!