Forum Discussion
Comparing SKU average with Category Average
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:
Anonymous 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 )
8 Replies
- parry2kSuper User
Anonymous can you share samle pbix file (remove sensitive data before sharing)
- AnonymousNot applicable
- danextianSuper User
Hi Anonymous ,
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] ) )- AnonymousNot applicable
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.
- danextianSuper User
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