Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

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.

 

neelofarshama_0-1617373289306.png

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

 

Any help would be highly appreciated.

 

Thanks In Advance,

Neelofar Shama.

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi @Anonymous ,

You could use the following steps:

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

v-luwang-msft_0-1617694672755.png

 

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

 

v-luwang-msft_1-1617694777683.png

 

 

 

Wish it is helpful for you !

 

Best Regards

Lucien

View solution in original post

2 REPLIES 2
v-luwang-msft
Community Support
Community Support

Hi @Anonymous ,

You could use the following steps:

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

v-luwang-msft_0-1617694672755.png

 

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

 

v-luwang-msft_1-1617694777683.png

 

 

 

Wish it is helpful for you !

 

Best Regards

Lucien

amitchandak
Super User
Super User

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

 

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

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.