Calculating difference between two calculated measures

[Werdinkaa]

Hi, 

I need to calculate variance between two measures however dax returns difference between summed up submeasures:

example

Sales = Calculate([Total],filter('Category'[KPI] = "Sales")

Other = Calculate([Total],filter('Category'[KPI] = "Other")

Result = [Sales]/[Other]

I have the same calculation for Actual and Forecast and using switch to show on matrix based on selected value

I have added another measuere "Difference" 

[Difference] = [Actual]-[Forecast]

It works perfectly for all other kpis however with division it doesnt calculate difference between Actual nr and Forecast nr ,but sums up Sales for both and Other for boths 

Can you help please?

[Anonymous]

Hi @Werdinkaa ,

Create a KPI table as below.

Then create measures as below.

Actual = 
var _sales = CALCULATE(SUM(Data[Amount]),FILTER(ALLSELECTED(Data),Data[Category]="actual"&&'Data'[KPI]="sales"&&Data[Period]=SELECTEDVALUE(Period[Period])))
var _other = CALCULATE(SUM(Data[Amount]),FILTER(ALLSELECTED(Data),Data[Category]="actual"&&'Data'[KPI]="other"&&Data[Period]=SELECTEDVALUE(Period[Period])))
var _percent = _sales/_other
return
IF(SELECTEDVALUE('KPI'[KPI])="%",_percent,CALCULATE(SUM(Data[Amount]),Data[Category]="actual"))

Forecast = 
var _sales = CALCULATE(SUM(Data[Amount]),FILTER(ALLSELECTED(Data),Data[Category]="forecast"&&'Data'[KPI]="sales"&&Data[Period]=SELECTEDVALUE(Period[Period])))
var _other = CALCULATE(SUM(Data[Amount]),FILTER(ALLSELECTED(Data),Data[Category]="forecast"&&'Data'[KPI]="other"&&Data[Period]=SELECTEDVALUE(Period[Period])))
var _percent = _sales/_other
return
IF(SELECTEDVALUE('KPI'[KPI])="%",_percent,CALCULATE(SUM(Data[Amount]),Data[Category]="forecast"))

variance = [Actual]-[Forecast] 

Result would be shown as below.

Pbix as attached.

Best Regards,

Jay

[Werdinkaa]

Hi @Anonymous , Thanks however this is not sufficient - I have only put example, in real model we have around 30-40 kpis based on few milion rows , and its not only actual forecast but other categorys too ( previous quaters etc). But thanks

[Werdinkaa]

@amitchandak hey there you have a link https://drive.google.com/file/d/1CBexdmPjfEx5hZJs-nYjaKMNiCL65zna/view?usp=sharing  I just made a quick sample

[Werdinkaa]

@amitchandak Hey, do you have any ideas for solution? I would really appriciate

[amitchandak]

@Werdinkaa , There seems to be a measure split on row and column?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Werdinkaa]

Hi @amitchandak Did the link work? Do you maybe have a solution?

Thanks in advance

[amitchandak]

@Werdinkaa , I check it, can you tell what is expected number is in the file, They are different from what you shared here