Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
_Junayetrahman
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 calculatedShown here how it would be calculated

1 ACCEPTED SOLUTION
Ahmedx
Super User
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

Screen Capture #265.png

 

View solution in original post

2 REPLIES 2
Ahmedx
Super User
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

Screen Capture #265.png

 

jaweher899
Impactful Individual
Impactful Individual

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

         

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.