Help with DAX Measure - Sum with condition

[MVCPA]

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 except "Normal Time" >= 164, then "Normal Time" should show zero. Else Show the difference between Employee A's all hours except "Normal Time" vs hours that are "Normal Time".
PS: They also want that in the totals.

Here's an example:

As seen in the Screenshot, Karen's hours <> "Normal Time" = 87.2 while the "Normal Time" is 174.8.
The expected result should be:
-Time Categories A,B,C,D,E,F,G should show the same. 
-Normal hours should show as 87.6

- Total Hours should be 174.8

I am really not sure if I shoud manipulate the data using DAX or Using M Query. I've been on this for days now, any help will be appreciated. Thank you!

[MVCPA]

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]

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]

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 · Accepted solution]

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]

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

[MVCPA]

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]

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.

[v-sathmakuri]

Hi @MVCPA ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Jihwan_Kim  and @danextian  for providing the response.

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

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the  below picture and the attached pbix file.

expected result measure: = 
SUMX (
    SUMMARIZECOLUMNS ( employee[employee], hours_type[hour_type] ),
    CALCULATE (
        IF (
            CALCULATE (
                SUM ( hours[hours] ),
                REMOVEFILTERS ( hours_type ),
                NOT ( hours_type[hour_type] IN { "Normal Time" } )
            ) >= 164,
            SWITCH (
                TRUE (),
                SELECTEDVALUE ( hours[hour_type] ) = "Normal Time", 0,
                SUM ( hours[hours] )
            ),
            SWITCH (
                TRUE (),
                SELECTEDVALUE ( hours[hour_type] ) = "Normal Time",
                    ABS (
                        CALCULATE (
                            SUM ( hours[hours] ),
                            REMOVEFILTERS ( hours_type ),
                            NOT ( hours_type[hour_type] IN { "Normal Time" } )
                        )
                            - SUM ( hours[hours] )
                    ),
                SUM ( hours[hours] )
            )
        )
    )
)

[danextian]

Hi @MVCPA 

Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX (confidential data removed) stored in the cloud. Just as it takes time to prepare your sample data, it also takes time to come up with a solution.

This sticky post should serve as a guide:

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...