Average variance by Item

[nok]

Hello!
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

I have already created a measure that calculates variance by ID&Item. So, based on this example table, the variance by ID&Item 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 variance results by ID&Item, I want to create now a measure that calculates the average of each variance per item. The expected result of this new measure would be this:

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'm having trouble creating this measure that averages variance per item. How can I do this?

[andrewsommer · Accepted solution]

Should be a simple two step process. 

Create a calculated table of unique ID-Item combinations with their variance

VariancePerIDItem = 
ADDCOLUMNS (
    SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[Item] ),
    "Variance", 
        VAR Prices = 
            CALCULATETABLE (
                VALUES('Table'[Price]),
                ALLEXCEPT('Table', 'Table'[ID], 'Table'[Item])
            )
        RETURN 
            DIVIDE ( MAXX(Prices, [Price]), MINX(Prices, [Price]) ) - 1
)

Then, create a measure that calculates the average of non-zero variances per item:

AverageVariancePerItem = 
AVERAGEX (
    FILTER (
        VariancePerIDItem,
        [Variance] <> 0
            && VariancePerIDItem[Item] = SELECTEDVALUE('Table'[Item])
    ),
    [Variance]
)

When you place Item in your visual and use this [AverageVariancePerItem] measure, you’ll get your expected result.

Please mark this post as solution if it helps you. Appreciate Kudos.