Average of Parent(s) value whilst choosing a child

[Parosel]

Hello, All

this is my first post so please be kind. I spent several hours trying to find a solution to this and was very close on a number of occasions but haven't been successful.

My problem is this. I have an excel table of data not disimilar to the one below. I have the prevalenace value for each team for each condition. However, I am trying to find the averag value for each parent of the team, so Department, Group and Organisation. So in my dashboard I can then compare Doctors sickness prevalence at Organisation A against other departments, groups in Organisation A but in all other organisations I have data for.

Like i say I was very close last night to finding a solution but the value I calculated was a fixed prevalence for each organisation which didn't change when slicing by condition.

Apologies if I have posted in the wrong area, if I have if someone could point me in the right direction that would be great or if someone sees this an it's a simple dax solution that would be great too. And I'm not precious about if I do it via a measure or calculated column - though as I write this a I'd need a calculated column for each condition and each level of my org hierarchy so probably a measure????

Condition	Organisation	Group	Department	Team	2020-21 Prevalence (%)
Sickness	Organisation A	Medical	Emergency	Doctors	3.42%
Sickness	Organisation A	Medical	Emergency	Nurses	2.40%
Sickness	Organisation A	Medical	Emergency	HCA	1.80%
Sickness	Organisation A	Medical	Emergency	Paramedic	2.90%
Stress	Organisation B	Medical	Emergency	Doctors	5.50%
Stress	Organisation B	Medical	Emergency	Nurses	6.90%
Stress	Organisation B	Medical	Emergency	HCA	4.90%
Stress	Organisation B	Medical	Emergency	Paramedic	7.80%

[Parosel]

@tamerj1 Sorry if my message wasn't clear but I don't have an excel formulas I just pulled the data in as a flat table into power bi and I'm trying to group the prevalence value so that I can compare team prevalence against their department, group and organisation average or any other organisation, group and department average for any condition.

[tamerj1]

@Parosel  

Apologies. I think I did not make my question clear enough. 

When you do the group by, do you need to sum the values, average them, take the max or min?

[Parosel]

@tamerj1 Yes exactly - I know I need to CALCULATE the AVERAGE of the prevalence by heirachy and condition but I don't the dax I need to create this measure. I've even used the "quick measure" function to filter by but that didn't work. 

[tamerj1]

@Parosel 

You can create a measure 

Prevalence% = 

AVERAGE ( 'Table'[Prevalence (%)] )

then use a matrix visual and drag the recuired columns into the rows. PowerBi will automatically create your hierarchy and the results will change Automatically based on slicer selection. 

[Parosel]

@tamerj1 Thanks for trying but I don't think I've been able to explain myself well enough. I'll keep looking. thanks 

[tamerj1]

@Parosel 

Sorry I wasn't able to help you and for not being able to fully understand your requirement. 
"However, I am trying to find the averag value for each parent of the team, so Department, Group and Organisation. So in my dashboard I can then compare Doctors sickness prevalence at Organisation A against other departments, groups in Organisation A but in all other organisations I have data for."

If you may please clarify further this point I might be able to support you. This is why I was asking for dimmy expected results even written by hand. Thanks have a great day

[Parosel]

@tamerj1 

My ultimate aim (using my table as example) is to have a report page when I have the 

prevalence for a Team and then the prevalence for the other hierachies and the have a slicer so I can change the conditions. At the minute when I change my slicer condition the slicer presents every heirarchy with the same prevalence so

Sickness:

Organisation 3.42%

Group 3.42%

Department 3.42%

Team 3.42%

But what I want is to have the team against the average of those heirachies above them so

Sickness

Organisation 3.98%

Group 4.12%

Department 3.89%

Team 3.42%

I have tried a different approach in duplicating my table so I also have tables for Department, Group and Organisation and averaging the prevalence and joining to my main table but this has not worked.

thanks again.

[tamerj1]

Hi @Parosel 
How did you come up with these numbers?

• Organisation 3.98%
• Group 4.12%
• Department 3.89%
• Team 3.42%

[Parosel]

@tamerj1 they were just examples to show that each level will have a different prevalence when/if I get it working the way I am trying to.

[tamerj1]

@Parosel sorry for not being able to provide suitable support to you. I hope others can do.
Good luck and have a great day!

[Parosel · Accepted solution]

@tamerj1 Hi, I just wanted to let you know that after a few more painful days I finally cracked it with a calculated column for each level of heirarchy: 

Organisation Average Prevalence = AVERAGEX(
FILTER(
Sheet1,Sheet1[Organisation]=EARLIER(Sheet1[Organisation])
&&Sheet1[Condition]=EARLIER(Sheet1[Condition])),Sheet1[Prevalence])

[tamerj1]

@Parosel 

This should be faster

Organisation Average Prevalence =
AVERAGEX (
    CALCULATETABLE (
        Sheet1,
        ALLEXCEPT ( Sheet1, Sheet1[Organisation], Sheet1[Condition] )
    ),
    Sheet1[Prevalence]
)

[tamerj1]

Hi @Parosel

welcome on board. You are in the right place but need to clarify further your targeted end results. If you wish you can share a screenshot of same calculation done on excel for example indicating the excel formula. Thanks and have agreat day!