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

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