Forum Discussion

ank1997's avatar
ank1997
Microsoft Employee
4 years ago
Solved

DAX Function for finding the average

Hi all!    I have been stuck on this for a while and was wondering if anyone can help me!   I have an excel that has a list of job ID numbers (some that are repeated, 200+ rows) in one column and...
  • v-cazheng-msft's avatar
    v-cazheng-msft
    4 years ago

    Hi ank1997 ,

     

    You may try this Measure if you still have problems on it.

    CurrentStatusAvgForPerID =

VAR CountStatus =

    CALCULATE (

        COUNT ( 'Table'[current_status] ),

        ALLEXCEPT ( 'Table', 'Table'[job_id], 'Table'[current_status] )

    )

VAR CountStatusPerID =

    CALCULATE (

        COUNT ( 'Table'[current_status] ),

        ALLEXCEPT ( 'Table', 'Table'[job_id] )

    )

VAR Table_ =

    SUMMARIZE (

        VALUES ( 'Table' ),

        'Table'[job_id],

        'Table'[current_status],

        "Div", CountStatus / CountStatusPerID

    )

RETURN

    SUMX ( Table_, [Div] )

     

    The result should look like this.

     

    Also, attached the pbix file as reference.

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

     

    Best Regards,

    Community Support Team _ Caiyun

Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

Hi,

I am not sure if I understood your question correctly.

I tried to create a sample data like below.

Please check the below picture and the attached pbix file.

 

 

expected result measure: =
VAR newtable =
ADDCOLUMNS (
VALUES ( Data[Job_ID] ),
"@rowcount", CALCULATE ( COUNTROWS ( Data ) )
)
RETURN
IF ( HASONEVALUE ( Data[Current_Status] ), AVERAGEX ( newtable, [@rowcount] ) )

ank1997's avatar
ank1997
Microsoft Employee
4 years ago

Thanks a lot! I tried this out and it worked great! Only issue that I'm having is that some Job IDs show blanks but I have a whole list of applications for it.