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 current application status (applied, rejected, screening) in another. I want to find the average number of applications(current status) per job ID but I have been stuck on getting this to work properly on my power BI. 

 

 

 

Any ideas on how I can do this? I appreciate the help in advance! 

  • 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

4 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi ank1997 

    Select matrix visual. Drag the ID's in the raws.  Drag current_status in the coulmns. 
    Create measure

    Number of Applications = COUNTROWS ( "table name" )

     Select any application type from the slicer and you'll get the number of its applications. 
    If you mean by "average" the percentage out of total then create measure

    % of Applications =
    [Number of Applications] / CALCULATE ( COUNTROWS ( "table name" ), ALL ( [current_status] )

    Drag the last measure into the values

  • 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. 

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Community Support
    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