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

Data_Girl_23's avatar
Data_Girl_23
Frequent Visitor
9 years ago

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

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