Average on total level from multiple measures

Question

hi community,&nbsp;I want to generate the average Bradford ratio per individual and per company. I have made the following Dax measures:&nbsp;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 period3. Bradford score: # of cases x 2 x # of sickness days&nbsp;I have the following outcome:&nbsp;Employee ID# of cases in period# of sickness days in periodBradford scoreE012520E0241080E033636E04000E051816E061010200E071918&nbsp;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).&nbsp;How can i generate the average of 53?&nbsp;Many thanks in advance.&nbsp;Regards,Frank

greg_deckler · Answer

frankhofmans&nbsp;First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742eThis 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/63376Also, 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/547907Also: https://youtu.be/uXRriTN0cfYAnd: https://youtu.be/n4TYhF2ARe8

v-zhangti · Answer

Hi,&nbsp;frankhofmans&nbsp;
&nbsp;
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.
&nbsp;
Best Regards,
Community Support Team _Charlotte
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

frankhofmans · Answer

Hi Charlotte,&nbsp;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?&nbsp;Many thanks,&nbsp;Regards,Frank

v-zhangti · Answer

Hi,&nbsp;frankhofmans&nbsp;
&nbsp;
Can you provide information on data that partially contains the name of the company?
&nbsp;
Best Regards

frankhofmans · Answer

hi,&nbsp;i have two tables:&nbsp;Table "Employments"&nbsp;Employee_IDEmployer_IDStartdate contractEnddate contract&nbsp;Table "Sickness cases"&nbsp;Employee_IDStartdate sicknessEnddate sickness&nbsp;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:&nbsp;Employer_IDAverage bradford factorE001xxxE002xxx&nbsp;When i use your formula, i get the same results on every row.&nbsp;Thanks in advance,&nbsp;Regards, Frank

Forum Discussion

Average on total level from multiple measures

5 Replies

Related content

Average of multiple measures

DAX Measure for Ranking the valid rows and calculating the average rating

Rolling 12M Average measure

Average of measure

Daily Average Over Multiple Years

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