Calculate average variance

[nok]

Hello!
Please note that all the columns and measures used in my report come from the same table, called "MyTable". So... I created a measure that calculates the price variance of the Items in my table, just like this:

variance = 
VAR var_Max = MAX( MyTable[Price] )
VAR var_Min = MIN( MyTable[Price] )
RETURN
    IF(
        NOT ISBLANK( var_Min ) && var_Min <> 0,
            IF(var_Max = var_Min,
                0,
                DIVIDE( var_Max, var_Min ) -1
            ),
            BLANK()       
    )

I want to create a measure similar to this one, but that calculates the average of the variance of the Item shown in my visual table. To clarify, I have separated two examples:

1st Example: in the image below, the table on the left shows the variance of the Item and the table on the right goes into more detail, showing the variance of this item for each ID (items can have different IDs).

With this new average measure, the table on the left should show the variance of 198,75%, since it would be the average of the only two IDs that exist for this Item in the table on the right (388,71/8,80 = 198,755).

2nd Example: In this example, we have a special case, since a set of ID&Item had a 0% variance, so it should not be counted for the calculation of the average. With this new average measure, the table on the left should show a variance of 37,71% (since there was an ID&Item with a 0,0% variance, and zero variance should not be used in this calculation).

So how can I create a measure similar to the one I created, but that calculates the average variance of ID&Item within the same Item, respecting these two conditions that I showed in both examples?

[v-achippa]

Hi @nok,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @lbendlin and @Ashish_Mathur for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue?
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[nok]

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)

[Ashish_Mathur]

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?

[lbendlin · Accepted solution]

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