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,

 

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

Data_Girl_23's avatar
Data_Girl_23
Frequent Visitor
9 years ago

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

 

  • Data_Girl_23's avatar
    Data_Girl_23
    Frequent Visitor
    9 years ago

    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.

     

    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])

  • 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?