Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX for variance

Hi,

I have a requirement to calculate financial report variance which has Actual and Forecast columns, ideally variance should be Actual-Forecast but since my user is looking for a different requirement as shown below. The forecast column for Period 1 and 2 are empty so there he wants actual to be displayed also, in periods where forecast is existing irrespective of actual values present or not he wants forecast to be displayed, which is same as "2+10" row.

 

Please help em to create DAX for "2+10" row DAX.

 

Any help would be highly appreciated.

 

Thanks In Advance,

Neelofar Shama.

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    Hi Anonymous ,

    You could use the following steps:

    Step1 ,import data and then transpose table, then apply and colse :

     

    Step 2,use the following measure:

    two and ten =
var test1=if(MAX('Table'[Column3])="Actual","ten and two",MAX('Table'[Column3]))    
var test2=IF(test1<>BLANK(),test1,MAX('Table'[Column2]))  
return test2

     

     

     

     

    Wish it is helpful for you !

     

    Best Regards

    Lucien

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try a measure like assuming actual and forecast are measures 

     

    Measure = if(isblank([actual], [forecast],[actual])

     

     

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    5 years ago

    Hi Anonymous ,

    You could use the following steps:

    Step1 ,import data and then transpose table, then apply and colse :

     

    Step 2,use the following measure:

    two and ten =
var test1=if(MAX('Table'[Column3])="Actual","ten and two",MAX('Table'[Column3]))    
var test2=IF(test1<>BLANK(),test1,MAX('Table'[Column2]))  
return test2

     

     

     

     

    Wish it is helpful for you !

     

    Best Regards

    Lucien