Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Solved! Go to 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
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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]
)
)
Proud to be a Super User!
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.
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 |
Proud to be a Super User!
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
@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
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Han_Solo can you share samle pbix file (remove sensitive data before sharing)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.