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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nmck86
Post Patron
Post Patron

Variance Analysis in Power BI

Hi All,

 

I have a quesiton with how to add a variance number beside the 2 fields that I have listed below. I would like to have a third column that highlights the variance or something that I can add that will let me knwo the difference between the 2 columns.

 

2017-04-17_15-27-52.png

 

Thanks in advance for your assistance.

 

Thanks,

Nichole

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

Hi @nmck86,

 

You can use below measure to calculate the variance , then use "line and clustered column chart" to show the result.

 

STDEVXP = 
var currType= LASTNONBLANK(Sheet1[Type],[Type])
var temp=STDEVX.P(SUMMARIZE(FILTER(ALL(Sheet1),[Type]=currType),[Type],[Year],"Total",SUM(Sheet1[Amount])),[Total])
return
POWER(temp,2)

 

Capture.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

12 REPLIES 12
v-shex-msft
Community Support
Community Support

Hi @nmck86,

 

You can use below measure to calculate the variance , then use "line and clustered column chart" to show the result.

 

STDEVXP = 
var currType= LASTNONBLANK(Sheet1[Type],[Type])
var temp=STDEVX.P(SUMMARIZE(FILTER(ALL(Sheet1),[Type]=currType),[Type],[Year],"Total",SUM(Sheet1[Amount])),[Total])
return
POWER(temp,2)

 

Capture.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Is there a way to change that varaince line to the difference and not some other #? what you have is similar to what I am trying to accomplish; however, when I view the image it isnt a difference on the line. For instance for your first line the differnce is +213. The second line is +33, etc. Can you assist with that?

Hi @nmck86,

 

The result is the calculate variance of summarized records(group by type), if you only want to know th diff between the summared records, you can try to use below formula:

 

Diff= 
var currType= LASTNONBLANK(Sheet1[Type],[Type])
var temp=SUMMARIZE(FILTER(ALL(Sheet1),[Type]=currType),[Type],[Year],"Total",SUM(Sheet1[Amount]))
return
MAXX(temp,[Total]) - MINX(temp,[Total])

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thank you, this works great.  The only problem I have is that when you are subtracting a MIN from a MAX you will always get a positive result and the variance here between 2016 and 2017 is not always positive.   A B C and F are positive changed but D and E should result in negative numbers.     I have been trying to make that work.  Can you help please.   Thanks

Hi @Data_Girl_23,

 

You can add a variable to check the year, and use year to ensure the symbol:

Diff= 
var currType= LASTNONBLANK(Sheet1[Type],[Type])
var temp=SUMMARIZE(FILTER(ALL(Sheet1),[Type]=currType),[Type],[Year],"Total",SUM(Sheet1[Amount]))
var diff=MAXX(temp,[Total]) - MINX(temp,[Total])
var symbol=IF(VALUE(MAXX(temp,[Year]))>=VALUE(MINX(temp,[Year])),1,-1)
return
diff*symbol

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Xiaoxin,  Thank you but I am still getting all positive results??

 

Capture Diff_4.JPG

Hi @Data_Girl_23,

 

I modified my formula, you can try it if it works on your side:

Diff = 
var currType= LASTNONBLANK(Sheet1[Type],[Type])
var temp=SUMMARIZE(FILTER(ALL(Sheet1),[Type]=currType),[Type],[Year],"Total",SUM(Sheet1[Amount]))
var diff=MAXX(temp,[Total]) - MINX(temp,[Total])
var maxYear=CALCULATE(MAX(Sheet1[Year]),FILTER(ALL(Sheet1),[Type]=currType&&[Total]=MAXX(temp,[Total])))
var symbol=SWITCH(maxYear,2015,-1,2016,1,0)
return
diff*symbol

Regards,

 

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Have a look at our Waterfall and Variance Chart:

https://dataviz.boutique/

FeatureSheetWaterfall6.PNGFeatureSheetVarianceOS.png

Thank you again @v-shex-msft.

 

I tried this today and received an error.  Capture Total error and var symbol only for noted years.JPG

 

I also noted you changed the calculation to ONLY work if the Year was specifically noted to be one of two years.   Since this is dummy data can we figure out how to make it work with your var symbol=IF(VALUE(MAXX(temp,[Year]))>=VALUE(MINX(temp,[Year])),1,-1)?    That way it would be able to be used universally regardless if there were two specific years or twenty or more.

 

Thanks for your patience.  I have already learned a lot just from this posting.

Do you think this could work when we DON"T have two fixed years?

Xiaoxi,

I am new to writing in DAX so I tried breaking down your formula and the issue is with the (VALUE(MAXX(temp,[Year]).   After adding new measures for each part of the Diff_4 formula I got the following table.    Each of the MINX Years and MAXX Years are the same and they should not be.  This is why we are getting all the positive results.   I just don't know how to fix it, sorry.Capture MAXX MINN Years.JPG

 

Diff_4=

var currType= LASTNONBLANK(Sheet1[Type],[Type])

var temp=SUMMARIZE(FILTER(ALL(Sheet1),[Type]=currType),[Type],[Year],"Total",SUM(Sheet1[Amount]))

var diff=MAXX(temp,[Total]) - MINX(temp,[Total])

var symbol=IF(VALUE(MAXX(temp,[Year]))>=VALUE(MINX(temp,[Year])),1,-1)

return

diff*symbol

 

MAXX Year =
var currType= LASTNONBLANK(Sheet1[Type],[Type])
var temp=SUMMARIZE(FILTER(ALL(Sheet1),[Type]=currType),[Type],[Year],"Total",SUM(Sheet1[Amount]))
var diff=MAXX(temp,[Total]) - MINX(temp,[Total])
var symbol=IF(VALUE(MAXX(temp,[Year]))>=VALUE(MINX(temp,[Year])),1,-1)
return
MAXX(temp,[Year])

 

MINX Year =
var currType= LASTNONBLANK(Sheet1[Type],[Type])
var temp=SUMMARIZE(FILTER(ALL(Sheet1),[Type]=currType),[Type],[Year],"Total",SUM(Sheet1[Amount]))
var diff=MAXX(temp,[Total]) - MINX(temp,[Total])
var symbol=IF(VALUE(MAXX(temp,[Year]))>=VALUE(MINX(temp,[Year])),1,-1)
return
MINX(temp,[Year])

Phil_Seamark
Employee
Employee

Hi @nmck86

 

 

What does your underlying data look like?  


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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