Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |