cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Average of one field based on condition of other column

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

1 ACCEPTED SOLUTION
Super User

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

2 REPLIES 2
Super User

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.