Comparing SKU average with Category Average

[Han_Solo]

Hello,

I'm reaching to you because I've encountered some problem I have hard time to solve. Either is really simple and I cannot come up with solution or it's pretty tricky.

I want to calculate Average Price for SKU and Compare it to average price of category for some group of SKUs. Best to show will be the matrix in rows I have [Category High], [Category Low], [SKU]

Average for SKU:

AVG_SKU = CALCULATE(SUM(searchspring[Price]) / SUM(searchspring[Inventory Count]))

and to the matrix I added average for category which fits to me:
AVG_CAT = CALCULATE([AVG_SKU], ALLEXCEPT(searchspring, searchspring[Category High], searchspring[Category Low]))

In matrix with category hierarchy it works perfectly fine. But I need to depict it on the stacked bar chart on which I have only SKU on axis. So simulating this on matrix would be only [SKU] in ROWS section 

Problem is that on the matrix with hierarchy AVG_SKU shows average for SKU and AVG_CAT shows average for [Category High] > [Category Low] hierarchy but in the matrix where only [SKU] column is in the rows, AVG_CAT shows the average for everything. So any chart like stacked bar chart which I need to use would depict the values the same way. 

Of course it's understandable for me and I get it why it shows average for everything not for particular category hierarchy (context) but is there a solution to show something like this?

I mean to show on chart/matrix with only [SKU] on rows average for mentioned category hierarchy?
For example if I have sku HEH-2223 I would like to have 2.36 instead of 4.42 (for AVG_CAT)?

Probably I could add calculated column with this but I use live connaction with SQL Analysis Services so I would prefer to use measures only. 

Thank you in advance for your help.

EDIT:
file:

download here from wetransfer 

[danextian]

Hi @Han_Solo ,

Try these:

Category Average = 
CALCULATE (
    AVERAGE ( 'searchspring'[Value] ),
    ALLEXCEPT ( 'searchspring', 'searchspring'[Category High] )
)


SKU Average = 
CALCULATE (
    AVERAGE ( 'searchspring'[Value] ),
    ALLEXCEPT (
        'searchspring',
        'searchspring'[Category High],
        'searchspring'[Category Low]
    )
)

[Han_Solo]

@danextian 

Thank you but in your calculation the only difference is the sum function and divide changed for average which does not help at all. I don't need to calculate average based on the count of rows but on the number of items. 

[danextian]

Is Item a separate column from SKU/High Level Category? And the average is based on the sum of each item divided  by the number of unique items like below?

Item	Value	Row Count
Item10	790	20
Item19	40	5
Item02	42	16
Average	290.67

[Han_Solo]

@danextian 

If I understand you correctly i think that is separate. 

Average is calculate by the sum of value for item divided by column with number of items 

[parry2k · Accepted solution]

@Han_Solo try this measure, it will get you what you are looking for.

Would appreciate Kudos 🙂 if my solution helped.

AVG_CAT_LOW = 
VAR __low = MAX ( searchspring[Category Low] )
RETURN
CALCULATE( 
    [AVG_SKU],
    ALL ( searchspring[SKU], searchspring[Category Low]), 
    searchspring[Category Low] = __low 
)

[Han_Solo]

Thank you, works flawless! @parry2k 

[parry2k]

@Han_Solo can you share samle pbix file (remove sensitive data before sharing)

[Han_Solo]

Here you have.  

@parry2k 

The date is in the link for wetransfer