Calculating the correct difference sales against forecast

[Fess67]

This should be simple enough and I have *almost* worked it out in another *pbix however not completely and close enough is not good enough 🙂 and now I find myself going around in circles not really moving forward and getting more and more frustrated as this is a simple thing to do in Excel but I am struggling with DAX.

I have 3 tables.  GroupList, Forecast, Sales

Forecast is a monthly value, Sales is a MTD value for each product in the GroupList.  The master excel is 60k+ rows.

I need to work out the difference between the current sales and the forecast value.  Both these numbers are correct however the calculation to subtract sales from forecast is defeating me.

This is how it looks in excel with expected results.

Thanks in advance,

[amitchandak · Accepted solution]

@Fess67 , Need relationship diagram. Can you share sample data and sample output in table format?

Ideally looking ta data with common product group and Date table you should be able to get answer

sum(Sales[sales]) -sum(Forecast[Forecast])

or

If(isblank(sum(Sales[sales]) ) ,_1*sum(Forecast[Forecast]),sum(Sales[sales]) -sum(Forecast[Forecast]))

[jdbuchanan71 · Accepted solution]

@Fess67 

Take a look at the sample .pbix attached.  Is shows how to set up the relationships and write the measures you are looking for.

[jdbuchanan71 · Accepted solution]

@Fess67 

Take a look at the sample .pbix attached.  Is shows how to set up the relationships and write the measures you are looking for.

[amitchandak · Accepted solution]

@Fess67 , Need relationship diagram. Can you share sample data and sample output in table format?

Ideally looking ta data with common product group and Date table you should be able to get answer

sum(Sales[sales]) -sum(Forecast[Forecast])

or

If(isblank(sum(Sales[sales]) ) ,_1*sum(Forecast[Forecast]),sum(Sales[sales]) -sum(Forecast[Forecast]))

[Fess67]

Relationships below,  I would attach pbix but do not see an option however I have seen other posts where pbix has been attached.

You see the problem even if I try the equations you suggest the forecast value is referenced in every row rather than as a singular value.

[Fess67]

Ok I changed the many to many to 1 to many and then did the equation as a measure.  I was doing it as a calculated column.  I did not understand that they work differently.

Thank you both for your assistance.

[jdbuchanan71 · Accepted solution]

The amounts are calculated in measures, not calculated columns.

Sales Amount = SUM ( Sales[Sales] )

Forecast Amount = SUM ( Forecast[Forecast] )

Gap = [Sales Amount] - [Forecast Amount]

Then you pull the Product Group from the Product Group table and add those 3 measures to the matrix visual. 

[Fess67]

Thanks.  1 more thing please.  I noticed that you created measures for the forecast and sales amounts when there were already summed.  Did you do this for the header so it did not show 'Sum of Forecast' or is there a mathematical reason for using them?  I did not create those measures, only the Gap measure and it worked ok.

[amitchandak]

@Fess67 , Try a measure not column.

sumx(summarize(Table,Table[product group],"_1" ,sum(Table[sales]),"_2" , max(Table[forecast])),[_1]-[_2])