Calculating delta between actuals and forecast in the same column

[Anonymous]

Hello

Is it possible to have a measure that calculates the delta between ex actuals and forecast, when i am using a flat excel file?

Or do i need to have the different scenarios in different columns? 

Year	Month	Scenario	Amount
2021	February	Forecast	100
2021	February	Actuals	95

[Anonymous]

I don tknow how to attach a external table. Sorry about that.

Month	Fiscal Year	Scenario	Account	Amount
November	FY21	Forecast	Net Revenue	100
November	FY21	P Forecast	Net Revenue	110
November	FY21	Actuals	Net Revenue	90
November	FY21	Last year	Net Revenue	95
November	FY21	Plan	Net Revenue	115
November	FY21	Forecast	COGS	80
November	FY21	P Forecast	COGS	81
November	FY21	Actuals	COGS	79
November	FY21	Last year	COGS	75

I am trying to get a delta result from comparing the various scenarious against eachother but holding year, account etc the same. 

So for a delta between line 3 and line 1 would have a delta of 10

Im a trying to build a simple P&L that shows:

Account	Actuals	Forecast	Delta
Net revenue	90	100	10

I would like to be able to calculate that delta with out having to have a column for each scenario.

Is that possible?

[ERD]

Hi @Anonymous ,

I'm not sure about the result you want to achieve (only to count delta between Actuals & Forecast / build the table with columns Account|Actuals|Forecast|Delta), but here is a simple measure just counting delta:

#ActualsVsForecastDelta = 
var actuals = CALCULATE(MAX(FindDelta[Amount]),
FindDelta[Scenario] = "Actuals")
var forecast = CALCULATE(MAX(FindDelta[Amount]),
FindDelta[Scenario] = "Forecast")
return
forecast - actuals

[ERD]

Hello @Anonymous ,

If you just have 2 scenarios (Actuals vs Forecast), then it might be easier to Pivot Scenario column in Power Query using Amount column as values. In this case you'll get 2 columns: Actuals column with values and Forecast column with values. Finding difference will be a very short and simple task in this case.

[Anonymous]

Hi ERD,

Thank you for the suggestion. My illustration above was just that. My data file is several thousands lines with 6 diffent scenarios. All the scenarios is listed in the same column and i would very much like to have a delta between all of them. Is that possible wihtout changing my data setup?

[ERD]

It would be easier to help if you provide a larger table with data, not in a picture, and a final result you want to achieve (might be a picture).