Forum Discussion

nmck86's avatar
nmck86
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's avatar
    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's avatar
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

Data_Girl_23's avatar
Data_Girl_23
Frequent Visitor
9 years ago

Thank you again Anonymous.

 

I tried this today and received an error.  

 

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.

  • Data_Girl_23's avatar
    Data_Girl_23
    Frequent Visitor
    9 years ago

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