Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

To Calculate Average attendance frequency (Excl. weeks for employee with full week absence)

Hi,

I have a requirement to calculate the "Total average attendance frequency" and "Total average Attendance frequency(Excl. employees with full absence)".

 

Dax calculation for "Total average attendance frequency" as below and working as expected.

Total average attendance frequency =
 VAR _WeekAvg = ADDCOLUMNS (

                            SUMMARIZE('Table',Table[Week],

                           "_Average", CALCULATE(AVERAGE(Table[Total Frequency]))

                            RETURN

                            IF(HASONEVALUE(TABLE[Week],

                           AVERAGE(Table[Total Frequency]),

                           AVERAGEX(_WeekAvg,[_Average]))

 

But I have issues in writing the DAX calculation for "Total average Attendance frequency(Excl. employees with full absence)."

Please find the detail requirement below with sample data,

 

 

I would appreciate if someone can help me with the DAX calculation that exclude the weeks for employee where Absence Count is matched with with No. of Working days(week) as above screenshot for ref.

 

Thanks

Harsha

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous 

    You can refer to the following measure

    Total average attendance frequency =
VAR a =
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Employee  ID]
            IN VALUES ( 'Table'[Employee  ID] )
                && [Absence Count] <> [No. of Working Days(Week)]
    )
RETURN
    DIVIDE (
        SUMX ( a, [Total Frequency] ),
        SUMX ( a, [No. of Working Days(Week)] )
    )

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous 

    You can refer to the following measure

    Total average attendance frequency =
VAR a =
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Employee  ID]
            IN VALUES ( 'Table'[Employee  ID] )
                && [Absence Count] <> [No. of Working Days(Week)]
    )
RETURN
    DIVIDE (
        SUMX ( a, [Total Frequency] ),
        SUMX ( a, [No. of Working Days(Week)] )
    )

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.