cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## 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.

EDIT:
file:

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV

## Re: Comparing SKU average with Category Average

@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
)
``````

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

8 REPLIES 8
Highlighted
Super User IV

## Re: Comparing SKU average with Category Average

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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Super User I

## Re: Comparing SKU average with Category Average

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!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Highlighted
Frequent Visitor

## Re: Comparing SKU average with Category Average

The date is in the link for wetransfer

Highlighted
Frequent Visitor

## Re: Comparing SKU average with Category Average

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.

Highlighted
Super User I

## Re: Comparing SKU average with Category Average

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!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Highlighted
Super User IV

## Re: Comparing SKU average with Category Average

@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
)
``````

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Frequent Visitor

## Re: Comparing SKU average with Category Average

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

Highlighted
Frequent Visitor

## Re: Comparing SKU average with Category Average

Thank you, works flawless! @parry2k

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

#### Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.