Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
we have been trying to calculate average of measures in one of our project works Like in the attched picture ,for the first table,compliance(comp) is calculated by dividing reading towards compliance by monthly frequency target and then we calculate a measure compliance% (shown here) where if this quotient is greater than 1 or 100%,we consider 1 or 100% otherwise only the quotient.Say here for the month of April,2009 , reading towards compliance=2 and monthly frequency target=1,then comp=2 divide by 1 which is 2 and then in measure compliance% we calculate like if 2>1 then 1 and convert it percent i.e. 100%.Similarly for June 2009 , reading towards compliance=0 and monthly frequency target=1,then comp=0 divide by 1 which is 0 and then in measure compliance% we calculate like 0<1 then 0 i.e.compliance itself.
formula for comp and compliance%
Comp= DIVIDE([Readings Towards Compliance],[Monthly Frequency Target])
Compliance % = IF(SUM(View_Compliance[Comp %])>1,1,SUM(View_Compliance[Comp %]))+0
This compliance% is shown across different months.
Now in the below visualization,we need to show average of compliance% measure calculated in above visualiization across all these rows like in this case iot will be (100%+100%+0%)/3 i.e.200%/3=66.6%.
Please let me know wether it is possible?It is quite urgent.Any help is much appreciated.
You are looking for AVERAGEX. https://msdn.microsoft.com/en-us/library/ee634546.aspx
If you want to average across months, I would expect someting like
=AVERAGEX(VALUES(Calendar[YearMonth]), [Compliance %])
Hi! I have similar issue. I have a table with several employees who I need to evaluate. There are ten criteria (columns). I want to show average score for each employee, or average per row.
post some sample data and expected result
Does it make sense? I have several employees, or several rows.
I have tried to use this formula. It works, but I have different skill sets. I have tried to create another Av. Score column, but am getting circular reference error. For another skill set I am using different column names.
Av. Score = divide(CALCULATE(SUMX('Employee Evaluation','Employee Evaluation'[Job Performance. Adaptability2]+'Employee Evaluation'[Job Performance. Attendance and Punctuality2]+'Employee Evaluation'[Job Performance. Execution2]+'Employee Evaluation'[Job Performance. Initiative2]+'Employee Evaluation'[Job Performance. Organization2]+'Employee Evaluation'[Job Performance. Problem Solving2]+'Employee Evaluation'[Job Performance. Process Improvement2]+'Employee Evaluation'[Job Performance. Productivity2]+'Employee Evaluation'[Job Performance. Quality2]+'Employee Evaluation'[Job Performance. Knowledge of Sales Trade])),10)
Use AllExcept function
Refer this:https://www.sqlbi.com/articles/understanding-circular-dependencies/
Perhaps I am doing someting incorrectly. I am getting syntax error when I am trying to add ALLEXCEPT to my formula above.
Could there be another way to calculate my average per row?
i must say, i can hardly read that, even after saving it to jy local, it could be old age, but might be useful to make it much bigger
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |