Forum Discussion

Post Patron

9 years ago

Solved

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 ...

Need Help

Show and Tell

Tips and Tricks

Anonymous

9 years ago

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)

Regards,

Xiaoxin Sheng

Anonymous

Not applicable

9 years ago

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