topic Re: Average of Parent(s) value whilst choosing a child in DAX Commands and Tips

Average of Parent(s) value whilst choosing a child

Parosel — Sun, 20 Mar 2022 10:03:46 GMT

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%

Re: Average of Parent(s) value whilst choosing a child

tamerj1 — Sun, 20 Mar 2022 10:36:09 GMT

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!

Re: Average of Parent(s) value whilst choosing a child

Parosel — Sun, 20 Mar 2022 10:40:42 GMT

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.

Re: Average of Parent(s) value whilst choosing a child

tamerj1 — Sun, 20 Mar 2022 12:04:10 GMT

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?

Re: Average of Parent(s) value whilst choosing a child

Parosel — Sun, 20 Mar 2022 12:20:16 GMT

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.

Re: Average of Parent(s) value whilst choosing a child

tamerj1 — Sun, 20 Mar 2022 12:26:40 GMT

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.

Re: Average of Parent(s) value whilst choosing a child

Parosel — Sun, 20 Mar 2022 12:57:16 GMT

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

Re: Average of Parent(s) value whilst choosing a child

tamerj1 — Sun, 20 Mar 2022 15:26:31 GMT

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

Re: Average of Parent(s) value whilst choosing a child

Parosel — Sun, 20 Mar 2022 16:16:40 GMT

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.

Re: Average of Parent(s) value whilst choosing a child

tamerj1 — Mon, 21 Mar 2022 10:17:12 GMT

Hi Parosel
How did you come up with these numbers?

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

Re: Average of Parent(s) value whilst choosing a child

Parosel — Mon, 21 Mar 2022 10:23:33 GMT

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.

Re: Average of Parent(s) value whilst choosing a child

tamerj1 — Mon, 21 Mar 2022 10:53:14 GMT

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

Re: Average of Parent(s) value whilst choosing a child

Parosel — Thu, 24 Mar 2022 17:03:46 GMT

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])

Re: Average of Parent(s) value whilst choosing a child

tamerj1 — Thu, 24 Mar 2022 17:12:00 GMT

Parosel

This should be faster

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