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])
You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
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)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,
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?
