cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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]

image.png

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 

image.png

 

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 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
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 
)

 

 






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.





View solution in original post

8 REPLIES 8
Highlighted
Super User IV
Super User IV

Re: Comparing SKU average with Category Average

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






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.





Highlighted
Super User I
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]
    )
)






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Highlighted
Frequent Visitor

Re: Comparing SKU average with Category Average

Here you have.  

@parry2k 

The date is in the link for wetransfer 

Highlighted
Frequent Visitor

Re: Comparing SKU average with Category Average

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

 

 

Highlighted
Super User I
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  





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Highlighted
Super User IV
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 
)

 

 






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.





View solution in original post

Highlighted
Frequent Visitor

Re: Comparing SKU average with Category Average

@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 

Highlighted
Frequent Visitor

Re: Comparing SKU average with Category Average

Thank you, works flawless! @parry2k 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors