Forum Discussion
Calculate total average
Hi all,
I am calculating a column called "Comletion".
The measurs are
- Inspection KPI 2 = COUNT('Inspection Staff'[Form ID])-'Employee Transaction'[84 Hours Rule ]
- 0 or 1 = IF('Inspection Staff'[Inspection Counts]=0, 0, IF('Inspection'[Inspection KPI 2]<0, 'Inspection Staff'[Inspection Counts]/[84 Hours Rule ],1))
- Completion =AVERAGEX ( VALUES ( 'Staff'[Staff Name]), 'Inspection'[0 or 1] )
Hi , Anonymous
According to the screenshot you gave, it seems that your problem is that you put the measure "Completion" in the matrix to achieve the effect you want, but placed in the same data grouping table visual has different values, as far as I know, your measure "Completion" refers to the measure value "0 or 1", and the average achieved in the grouping case of the field "'Staff'[Staff Name])" is solved. But this "'Staff'[Staff Name])" is not placed in the table visual, and I would like to check with you if the "Staff'[Staff Name])" field is placed in the "Column" field of your matrix, because I see that it has a hierarchy in the columns:
If there is, I think you need to put the same "'Staff'[Staff Name])" field in the table visual for further grouping, because the value operation of your measure is actually based on the current visual context, and for the card graph, it does not have any grouping context conditions, and will be calculated according to the total value, so I think this "80%" is to find an Average that is calculated according to the total value.
In summary, the output of your metric values in all visuals depends on the context conditions in those visuals, and you need to be completely consistent to check that their expected output is consistent.
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
- v-yueyunzh-msft
Community Support
Hi , Anonymous
According to the screenshot you gave, it seems that your problem is that you put the measure "Completion" in the matrix to achieve the effect you want, but placed in the same data grouping table visual has different values, as far as I know, your measure "Completion" refers to the measure value "0 or 1", and the average achieved in the grouping case of the field "'Staff'[Staff Name])" is solved. But this "'Staff'[Staff Name])" is not placed in the table visual, and I would like to check with you if the "Staff'[Staff Name])" field is placed in the "Column" field of your matrix, because I see that it has a hierarchy in the columns:
If there is, I think you need to put the same "'Staff'[Staff Name])" field in the table visual for further grouping, because the value operation of your measure is actually based on the current visual context, and for the card graph, it does not have any grouping context conditions, and will be calculated according to the total value, so I think this "80%" is to find an Average that is calculated according to the total value.
In summary, the output of your metric values in all visuals depends on the context conditions in those visuals, and you need to be completely consistent to check that their expected output is consistent.
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
I have already addressed the issue. I re-write the measure of "completion" as follow:
Completion for Job = DIVIDE(SUMX(VALUES('Staff'[User Name]),'Inspection'[0 or 1]),COUNTX(VALUES('Staff'[User Name]),'Inspection Staff'[Inspection Counts for Inspection Crew]))Now the outcoming from both matrix table and graph are equal. But the total completion in card is still not right, I am still working on it.