Average of one field based on condition of other column

[_Junayetrahman]

Hi, I need help on DAX while calculating the average of Ecode. Condition would be if count of S code is matching with the distinct count of E code it will exclude while calculating average. If count of S code is not matching with the distinct count of E code then it will take distinct count of E code and based on that I need to calculate average. What should be the approach. Any help would be really appreciated.Shown here how it would be calculated

[Ahmedx · Accepted solution]

Is this what you are looking for?

Control = VAR _DisE =DISTINCTCOUNT('Table'[E Code])
          VAR _CounS = COUNT('Table'[S Code]) 
          VAR _Cont = IF(_DisE<> _CounS,_DisE)
RETURN
_Cont

Flag = IF([Control],1,0)


Final = var _t1 = SUMX(VALUES('Table'[S Code]),[Control])
        VAR _t2 = SUMX(VALUES('Table'[S Code]),[Flag])
RETURN
_t1/_t2

Sample PBIX file attached
https://dropmefiles.com/EJ14P

[jaweher899]

Here is one approach to solve the problem in DAX:

1. Create a calculated column that checks if the count of S code matches the distinct count of E code.

   Match = COUNT(S_Code) = DISTINCTCOUNT(E_Code)

   1. Create another calculated column to get the count of distinct E code based on the previous calculated column.

      E_Code_Count = IF([Match]=TRUE(),0,DISTINCTCOUNT(E_Code))

      1. Create a measure to calculate the average of E code based on the previous calculated column.
          

         Avg_E_Code = AVERAGEX(FILTER(ALL(Table),[E_Code_Count]>0),[E_Code])