Calculate total average

[Anonymous]

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

All the numbers in hte matrix table are right. But if I use job name and completion to make a table, the completion number are wired. And I pull the completion in a card, the number is also wrong.

I tried to open the direction control to "both" between 'Inspection Staff' and 'Staff', the number in job table are the same as matrix table, but the matrix table is wrong, all the number with 0 in '0 or 1' is not counted in 'Completion' column.

Could anyone help to write a new measure to make the number right in the card and in the job table?

Thank you in advance!

Branko

[v-yueyunzh-msft · Accepted solution]

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

[Anonymous]

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.