DAX: How to calculate manual average based on grouping

0
votes

I have a table with ID, Fault_Count, and ID_GROUP

ID contains multiple duplicates so my formula to calculate AVG Fault_Count in Dax was:=SUM([Fault_Count])/DISTINCTCOUNT([ID]) which gave me a correct number.

However the problem occurs when I try showing the AVG fault_count (per ID) by the ID group (ID group consist of 5 unique values: A,B,C,D,E)

For example: In a pivot table ID_Group "A" shows 15.66 however, if manually calculated, the number should be 9 because the the distinctcount of ID for group A was 3 and total fault count for group A was 27. However, the "grand total" is the correct value.

Is it because the formula does not contain any grouping to restrict calculation on the ID_Group?

Appreciate the help!

1
groupingdax

1 Answers

0
votes

NVM, I am an idiot. I was using the count of "fault_Count" instead of the SUM of the fault_Count. All checks out, no need for a grouping filter.