Forum Discussion

nok's avatar
nok
Advocate II
1 year ago
Solved

Average variance by Item

Hello!
My table has this structure:

ID       Item        Price
111Pen10
111Pen10
222Pen15
222Pen17
333Pen13
333Pen5
333Rubber10
333Rubber20
444Rubber22
444Rubber19
444Rubber26


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
Pen0,86%
Rubber0,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?

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • andrewsommer's avatar
    andrewsommer
    1 year ago

    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.

1 Reply

  • andrewsommer's avatar
    andrewsommer
    Super User
    1 year ago

    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.