Calculate row level sum from 2 fact tables

[arjenbos91]

I want to calculate an absolute forecast error based on 2 tables. I have made some sample data which reflects my problem:

• Fact: Sales (observation per date and category)
• Fact: Forecast (sales forecast per date)
• Dim: Date

Currently using these relationships:

In this example I've prepared the columns 'Forecast'[Actual Amount] and 'Forecast'[Absolute Error] by hand in Excel. But in my real data these are missing and I wish to calculate those with DAX.

The data looks like this:

The results in the blue table is what I want to achieve. The problem is that the Sales table also has an extra dimenion (the Category) and I can't join the Sales and Forecast into 1 table.

When using a simple

Absolute Forecast Error = ABS(SUM(Forecast[Forecast]) - SUM(Sales[Amount]))

it calculates the Total wrong. It gives a Total of 1. Which makes sense when substracting both totals. But the Total should be 7. How can I achieve this?

My actual data has a lot more dates and I wish to aggregate the absolute forecast error to months, quarters and years.

[IIPowerBlog]

hi @arjenbos91  check out my blogpost for this (the part where you create the measure in the SUM() OVER partition by logic ) hope this helps.  : https://www.iipowerblog.com/post/sum-over-partition-by-in-powerbi 

[arjenbos91]

Sorry but this doesn't suit my case. I don't need a separate totals column. My measure needs to aggregate correctly.

The case in your blog I would simply solve by using a table visual using the quantity as values and productname as either rows or columns. No DAX needed. Also the code in your blog has one of the Power BI pitfalls: Avoid using FILTER in CALCULATE

Thanks anyway

[IIPowerBlog]

ok thanks for the feedback, its much appreciated. Good luck with getting the solution!

[Barthel]

Hey @arjenbos91 
Instead of making the final result absolute, you should make each line absolute, and take the sum of them. You can do this within a row context, using the SUMX function. You want to do this per date, so date is your relevant row context in this case. You can use these measures:

forecast =
SUM ( Forecast[Forecast] )

sales_amount =
SUM ( Sales[Amount] )

Absolute Forecast Error =
SUMX ( Date, ABS ( [forecast] - [sales_amount] ) )

[arjenbos91]

Thanks this works!

I've done some more experimenting and I only get the correct result when I use multiple measures (as in your answer). Why doesn't the following work?

Absolute Forecast Error = SUMX('Date', ABS(SUM(Forecast[Forecast]) - SUM(Sales[Amount]))

Or this

Absolute Forecast Error =
VAR f = SUM(Forecast[Forecast])
VAR s = SUM(Sales[Amount])
RETURN SUMX('Date', ABS(f - s))

?

[Barthel · Accepted solution]

@arjenbos91 
This is because you have to calculate the amount per date. The above variants calculate the totals and do not take the row context into account, because context transition does not take place. The referenced measures implicitly activate context transition. You could also process it in to one measure by using CALCULATE (which also activates context transition):

Absolute Forecast Error =
SUMX (
    'Date',
    ABS (
        CALCULATE ( SUM ( Forecast[Forecast] ) ) - CALCULATE ( SUM ( Sales[Amount] ) )
    )
)

For more info see this article: https://www.sqlbi.com/articles/understanding-context-transition-in-dax/