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.

MVCPA's avatar
MVCPA
Frequent Visitor
10 months ago

Hi Jihwan_Kim 

 

Thank you for your response. After some checks, the formula you gave me worked.

BUT I was given another requirement. Another filter was required in rows. I have attached a sample PIBX here.

 

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