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. I really appreciate it.!This is how my model looks like:

 

 

I have patented my work to your example. I am just not lucky to have the same results as yours. As you can see, my expected results multiplied the non-"Normal Time" by 2. I am not sure why.
Please see the results:

 


This is the measure that I used :

Expected Value =

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

            SWITCH(
                TRUE(),
                SELECTEDVALUE('Hours Type'[Time Type]) = "Normal Time", 0,
                SUM(Hours[crc27_hours])
            ),
            SWITCH(
                TRUE(),
                SELECTEDVALUE('Hours Type'[Time Type]) = "Normal Time",
                    ABS(
                        CALCULATE(
                            SUM(Hours[crc27_hours]),
                            REMOVEFILTERS( 'Hours Type'[Time Type]),
                            NOT ('Hours Type'[Time Type] IN {"Normal Time"})
                        )
                        - SUM( Hours[crc27_hours])
                    ),
                    SUM(Hours[crc27_hours])
            )
        )
    )
   
)
 

 

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    10 months ago

    Hi,

    If it is OK, please recreate a sample pbix file, and please share your sample pbix file's link.

    And then, I can try to look into it.

    Thank you.