Creating a report of differences between two measures from 2 sources

[armastan]

Hi
I hope this is a simple question that someone could help me with.

I have budget/forecast/actual numbers imported into the data model on separate sheets. There are additional 3 tables that all the data is connected to: products, customers and months that are the rows and column for Pivot tables.

My goal is to show difference between two measures in a Pivot tabel: budget vs actual numbers for specific product/customer/month.
Should I calculate the difference as a calculated field in the Data Model with DAX or is there another way? If yes, then where (on what sheet) and could I calculate it if actual and budget sheets may have different customers/product number (e.g product was budgeted, but does not appear in actual numbers)?

Thanks in advance!

 

[ImkeF]

This sounds like the standard-Power-Pivot setup where you write a measure for one purpose that would deliver the correct results in every filter context/the pivot-tables you build upon them.
Actual should be one measure, Budget another one and difference the third: Diff:=[Budget] - [Actual]

 

[armastan]

Hi! In what table this measure should be created? I've trid to add this in ACT data table and got the following error message :

Calculation error in measure 'ACTdata'[Act vs BGT]: The value for column 'Invoiced (EUR)' in table 'ACTdata' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.

So just writing act-bgt formula does not seem to work. Or Am I doing something wrong? ... Very much hope for your help

 

[ImkeF]

Sorry, cannot say from here. Assuming you didn't write "act-bgt" but were actually subtracting the measures: [act]-[bgt].
Do the single measures [act] and [bgt] work alright?

Please post sample file with datamodel for further investigation.

 

[armastan]

I added sum infront of the measure and that helped!