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.

Jihwan_Kim's avatar
Jihwan_Kim
Super User
10 months ago

Hi,
Please check the below if it provides the expected result.

Thank you.

Expected Value = 

SUMX(
    SUMMARIZECOLUMNS(employee[crc27_employee],'Time type'[crc27_timetype], Indirects[Indirects]),
    CALCULATE(
        IF(
            CALCULATE(
                SUM('Sample'[crc27_hours]),
                REMOVEFILTERS('Time type'),
                NOT ('Time type'[crc27_timetype] IN {"Normal Time"})
            )>=164.67,

            SWITCH(
                TRUE(),
                SELECTEDVALUE('Time type'[crc27_timetype]) = "Normal Time", 0,
                SUM('Sample'[crc27_hours])
            ),
            SWITCH(
                TRUE(),
                SELECTEDVALUE('Time type'[crc27_timetype]) = "Normal Time",
                    ABS(
                        CALCULATE(
                            SUM('Sample'[crc27_hours]),
                            REMOVEFILTERS( 'Time type'),
                            NOT ('Time type'[crc27_timetype] IN {"Normal Time"})
                        )
                        - SUM( 'Sample'[crc27_hours])
                    ),
                    SUM('Sample'[crc27_hours])
            )
        )
    )
    
)
MVCPA's avatar
MVCPA
Frequent Visitor
10 months ago

Hi Jihwan_Kim !

 

Thank you for the providing a solution. I tried replicating it but I had this result:

 

As you can see Karen now shows "Normal Time" under "Direct". Am I missing something? Thanks again!

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