Average on total level from multiple measures

[frankhofmans]

hi community,

I want to generate the average Bradford ratio per individual and per company. I have made the following Dax measures:

1. Number of sickness cases in selected period (all sickness cases with at least 1 sickness day in selected period)

2. Number of sickness days in selected period

3. Bradford score: # of cases x 2 x # of sickness days

I have the following outcome:

Employee ID	# of cases in period	# of sickness days in period	Bradford score
E01	2	5	20
E02	4	10	80
E03	3	6	36
E04	0	0	0
E05	1	8	16
E06	10	10	200
E07	1	9	18

The average Bradford score of the company is 53 (average of alle individuals), but i get 2016 as a result (21 cases x 2 x 48 sickness days).

How can i generate the average of 53?

Many thanks in advance.

Regards,

Frank

[v-zhangti]

Hi, @frankhofmans 

You can try the following methods.

Average = Var _table=SUMMARIZE(ALL('Table'),'Table'[Employee ID],"Sum",[Bradford score])
Return
AVERAGEX(_table,[Sum])

Is this the result you expect? Please see the attached document.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[frankhofmans]

Hi Charlotte,

this formula works, but i select a company (column "Company_Name" in Employee table), then the value doesn't change. How can i adjust this formula to get a average Bradford factor per company?

Many thanks,

Regards,

Frank

[v-zhangti]

Hi, @frankhofmans 

Can you provide information on data that partially contains the name of the company?

Best Regards

[frankhofmans]

hi,

i have two tables:

Table "Employments"

Employee_ID

Employer_ID

Startdate contract

Enddate contract

Table "Sickness cases"

Employee_ID

Startdate sickness

Enddate sickness

I've calculated the number of unique employees with at least one working day in selected periode (from the employee_table) and the number of sickness cases and the total duration of the sickness cases per employee. I want to create the following table:

Employer_ID	Average bradford factor
E001	xxx
E002	xxx

When i use your formula, i get the same results on every row.

Thanks in advance,

Regards, Frank

[Greg_Deckler]

@frankhofmans First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8