Forum Discussion

MVCPA's avatar
MVCPA
Frequent Visitor
10 months ago
Solved

Help with DAX Measure - Sum with condition

Hi all!,   I have table that contains a list of employees and their timesheet entries. Currently, my manager wants to apply a logic and show it up in a Matrix.   Logic: IF Employee A's all hours ...
Need Help
Tutorial Requests
  • v-sathmakuri's avatar
    v-sathmakuri
    9 months ago

    Hi MVCPA ,

     

    Sorry for the delay in response!!

     

    Below is the updated measure which is showing normal time only for indirect for karen employee. Attached pbix file for reference.

     

    Expected Value =
    SUMX(
        SUMMARIZECOLUMNS(
            employee[crc27_employee],
            'Time type'[crc27_timetype]
        ),
        VAR CurrentHours =
            CALCULATE(
                SUM('Sample'[crc27_hours])
            )
        VAR HasData =
            NOT ISBLANK(CurrentHours)
        VAR NonNormalHours =
            CALCULATE(
                SUM('Sample'[crc27_hours]),
                REMOVEFILTERS('Time type'),
                NOT('Time type'[crc27_timetype] IN {"Normal Time"})
            )
        RETURN
            IF(
                HasData,
                IF(
                    NonNormalHours >= 164.67,
                    SWITCH(
                        TRUE(),
                        SELECTEDVALUE('Time type'[crc27_timetype]) = "Normal Time", 0,
                        CurrentHours
                    ),
                    SWITCH(
                        TRUE(),
                        SELECTEDVALUE('Time type'[crc27_timetype]) = "Normal Time",
                        ABS(NonNormalHours - CurrentHours),
                        CurrentHours
                    )
                ),
                BLANK()
            )
    )
     
     

    Please review the output and let us know if you still need any further assistance here.

     

    Thanks & Regards,

    Rekha Athmakuri.

v-sathmakuri's avatar
v-sathmakuri
Community Support
9 months ago

Hi MVCPA ,

 

Sorry for the delay in response!!

 

Below is the updated measure which is showing normal time only for indirect for karen employee. Attached pbix file for reference.

 

Expected Value =
SUMX(
    SUMMARIZECOLUMNS(
        employee[crc27_employee],
        'Time type'[crc27_timetype]
    ),
    VAR CurrentHours =
        CALCULATE(
            SUM('Sample'[crc27_hours])
        )
    VAR HasData =
        NOT ISBLANK(CurrentHours)
    VAR NonNormalHours =
        CALCULATE(
            SUM('Sample'[crc27_hours]),
            REMOVEFILTERS('Time type'),
            NOT('Time type'[crc27_timetype] IN {"Normal Time"})
        )
    RETURN
        IF(
            HasData,
            IF(
                NonNormalHours >= 164.67,
                SWITCH(
                    TRUE(),
                    SELECTEDVALUE('Time type'[crc27_timetype]) = "Normal Time", 0,
                    CurrentHours
                ),
                SWITCH(
                    TRUE(),
                    SELECTEDVALUE('Time type'[crc27_timetype]) = "Normal Time",
                    ABS(NonNormalHours - CurrentHours),
                    CurrentHours
                )
            ),
            BLANK()
        )
)
 
 

Please review the output and let us know if you still need any further assistance here.

 

Thanks & Regards,

Rekha Athmakuri.

v-sathmakuri's avatar
v-sathmakuri
Community Support
9 months ago

Hi MVCPA ,

 

Could you please let us know if the provided response helped in resolving the issue. If you have any further questions we are happy to address.

 

Thank you!!