Counting Categories after Comparing Average Values of Categories with Global Average

[pbininja1]

Hello All,

My dataset is in the below format -

I have slicers for level 3 region. Based on selected a region, I want to present how many level 2 regions' average is more than the level 3 region's average. For example, if I select the level 3 region as A, then I want to show - "<2> areas' average is more than the regional average." (because average of level 2 region a1 and a2 is more than the regional average of 11.7). I can represent the value <2> by using a card, but how do I calculate this final value?

Please help - Thank you for all your time and effort!

I couldn't find a solution to the problem. If anyone finds it, please share.

[Mariusz]

Hi @pbininja1 

Can you create a sample of what you expect as an outcome?

Best Regards,
Mariusz

Please feel free to connect with me.

[pbininja1]

Based on the table shown in the question, I want to do the following - 

1. Show a message: "<x> number of level 2 regions have average more than the level 3 region"

2. Display a bar chart/table with details about only those level 2 regions whose average is more than the level 3 average

[Mariusz]

Hi @pbininja1 

It would really help if you could create a table in eg. Excel and show the result you are expecting based on your data sample included in your original post.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[ChrisMendoza · Accepted solution]

@pbininja1 -

I am not sure this is what you're going for; maybe enough to get you started?

Measure =
VAR lvl3GroupAvg =
    CALCULATE (
        AVERAGE ( TableName[Value] ),
        ALLEXCEPT ( TableName, TableName[Level 3 Region] )
    )
VAR lvl2GroupAvg =
    CALCULATE (
        AVERAGE ( TableName[Value] ),
        ALLEXCEPT ( TableName, TableName[Level 2 Region] )
    )
RETURN
    IF ( lvl2GroupAvg > lvl3GroupAvg, lvl2GroupAvg )

[pbininja1]

Thank you Chris!

@ChrisMendoza How do I calculate the count of the result of the above measure?

[ChrisMendoza]

@pbininja1-

---

@pbininja1 wrote:

@ChrisMendoza How do I calculate the count of the result of the above measure?

---

What are you trying to count?

[pbininja1]

I am counting number of level 2 regions based on the result from the measure suggested by you.