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

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.

10 Replies

  • danextian's avatar
    danextian
    Super User
    10 months ago

    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/1447523

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    10 months ago

    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] )
            )
        )
    )
)

     

  • v-sathmakuri's avatar
    v-sathmakuri
    Community Support
    10 months ago

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

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

       

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