Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 add that will let me knwo the difference between the 2 columns.
Thanks in advance for your assistance.
Thanks,
Nichole
Solved! Go to Solution.
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
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
Is there a way to change that varaince line to the difference and not some other #? what you have is similar to what I am trying to accomplish; however, when I view the image it isnt a difference on the line. For instance for your first line the differnce is +213. The second line is +33, etc. Can you assist with that?
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
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
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
Xiaoxin, Thank you but I am still getting all positive results??
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
Thank you again @v-shex-msft.
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.
Do you think this could work when we DON"T have two fixed years?
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])
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |