Forum Discussion
Calculate average variance
I think we have different understanding of what Variance means.
Variance is the expected value of the squared variation of a random variable from its mean value, in probability and statistics. Informally, variance estimates how far a set of numbers (random) are spread out from their mean value.
The value of variance is equal to the square of standard deviation.
Based on your sample data here is the variance of the individual groups
If you want the average of the variance then you need to "Think like the Grand Total" and materialize the variances for all groups.
variance = var a = ADDCOLUMNS(SUMMARIZECOLUMNS(MyTable[ID ],MyTable[Item ]),"v",CALCULATE(var.p(MyTable[Price]))) return AVERAGEX(a,[v])
Hi, lbendlin . Thanks for the reply!
I can't create a sample data report because I think I can't even share the sample data path in a pbix file.
But my table has this structure:
| ID | Item | Price |
| 111 | Pen | 10 |
| 111 | Pen | 10 |
| 222 | Pen | 15 |
| 222 | Pen | 17 |
| 333 | Pen | 13 |
| 333 | Pen | 5 |
| 333 | Rubber | 10 |
| 333 | Rubber | 20 |
| 444 | Rubber | 22 |
| 444 | Rubber | 19 |
| 444 | Rubber | 26 |
So, based on this example table, the variance by ID&Item (represented as the tables on the right in my original post) would be:
111, Pen -> (10/10) -1 = 0%
222, Pen -> (17/15) -1 = 0,13%
333, Pen -> (13/5) -1 = 1,6%
333, Rubber -> (20/20) -1 = 0%
444, Rubber -> (26/19) -1 = 0,36%
And based on this structure and in the variance results by ID&Item, this average measure would show this value for each Item:
| Item | Variance |
| Pen | 0,86% |
| Rubber | 0,36% |
Explanation:
Pen -> (0,13% + 1,6%)/2 = 0,86% (any ID&Item that has a 0% variation should not be considered in the average calculation)
Rubber -> (0,36%)/1 = 0,36% (any ID&Item that has a 0% variation should not be considered in the average calculation)
Hi,
Your explanation is inconsistent and confusing. For 222 why is the calculation 17/15 and for 333 as 13/5 (why not 5/13)? Also, shouldn't there be a date column as well?