Divergent values in the average calculation

[lcfaria]

Hi everyone,

I'm trying to calculate the average by manager, but this has given me different results depending on the filter applied.

When I have the unit and manager in the table, the result presented is correct, the average of all managers for unit A is 2.82%.

However, when I remove the managers from the table and keep only the unit, the value presented is different, 2.07% and this value is incorrect.

I've racked my brains trying to find the problem, but I couldn't, so I turn to the community to see if anyone can help me.

I am attaching a sample file with the problem presented: https://1drv.ms/u/s!At5JlMR_naI1gf41c2mHw5cyNda9bg?e=tq81Rz

Thank you!

[aj1973 · Accepted solution]

Hi @lcfaria 

The 2.07% is the correct one. it represents the SUM of [Bonus] = 62 divided by the DISTINCTCOUNT of the number of managers that = 30 of the Unit A.

[lcfaria]

Hi @aj1973, 

I already realized where the problem was, although I have 30 distinct managers, it should only consider those that have a target > 0, in the case of the example, 22 managers.

62 / 22 = 2.82%

Although I had a filter applied to the visual only for target > 0:

When I removed the managers from the table and kept only the unit, the measure stopped considering the 22 managers and started to consider all 30 (I still don't understand why this failed to consider the filter applied in the image above).

I made a simple change in the measure DAX and it solved the problem:

Your answer helped me find the problem, thank you very much!