DAX Function for finding the average

[ank1997]

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 · Accepted solution]

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]

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]

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. 

[tamerj1]

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