Forum Discussion

Werdinkaa's avatar
Werdinkaa
Frequent Visitor
5 years ago

Calculating difference between two calculated measures

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?

 

 

 

Need Help

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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.

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Werdinkaa
      Frequent Visitor
      5 years ago

      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