Forum Discussion

PAschieri's avatar
PAschieri
Frequent Visitor
1 year ago
Solved

Difficult Sum Measure

Hi guys, I´m trying to get the following DAX:   If the company has activities > 0 for code 100, then SUM all hours for that company. If the company has no activities for code 100, then Hours = 0. ...
Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    HI PAschieri ,
    Understood now, please try the bellow measure:

    Calculated Hours = 
VAR HasActivitiesCode100 = 
    CALCULATE(
        SUM('Activities'[Activities]),
        'Activities'[Code] = 100
    ) > 0

RETURN
IF(
    HasActivitiesCode100, 
    CALCULATE(SUM('Hours'[Hours]), ALLEXCEPT('Company', 'Company'[Name])),
    CALCULATE(SUM('Hours'[Hours]), ALLEXCEPT('Company', 'Company'[Name]))
)

     

     

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi PAschieri ,

    Thanks for all the replies!
    And PAschieri , I create sample data myself:

    You can use this DAX to create a measure:

    TotalHoursWithCondition = 
VAR _table =
    CALCULATETABLE (
        VALUES ( Data[Company] ),
        FILTER ( ALL ( Data ), 'Data'[Code] = 100 )
    )
RETURN
    CALCULATE ( SUM ( 'Data'[Hours] ), ALL ( Data ), 'Data'[Company] IN _table )

    And the final output is as below:


    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

Based on your description if company has no activities then the hours should be 0, so in your exepected result for B company, the measure should show 0, no?

If yes, please use this DAX bellow and let me know if its what you want.

Calculated Hours = 
VAR HasActivitiesCode100 = 
    CALCULATE(
        SUM('Activities'[Activities]),
        'Activities'[Code] = 100
    ) > 0

RETURN
IF(HasActivitiesCode100, SUM('Hours'[Hours]), 0)

 

Make sure to replace columns and table names with your owns.

 

 

  • PAschieri's avatar
    PAschieri
    Frequent Visitor
    1 year ago

    Hi Bibiano. No, it should not indicate 0 for company B, that´s the difficult part of the measure.

     

    No matter if the company has activities or no, the result should indicate 45 for all companies.

    • Bibiano_Geraldo's avatar
      Bibiano_Geraldo
      Super User
      1 year ago

      HI PAschieri ,
      Understood now, please try the bellow measure:

      Calculated Hours = 
VAR HasActivitiesCode100 = 
    CALCULATE(
        SUM('Activities'[Activities]),
        'Activities'[Code] = 100
    ) > 0

RETURN
IF(
    HasActivitiesCode100, 
    CALCULATE(SUM('Hours'[Hours]), ALLEXCEPT('Company', 'Company'[Name])),
    CALCULATE(SUM('Hours'[Hours]), ALLEXCEPT('Company', 'Company'[Name]))
)

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi PAschieri ,

      Thanks for all the replies!
      And PAschieri , I create sample data myself:

      You can use this DAX to create a measure:

      TotalHoursWithCondition = 
VAR _table =
    CALCULATETABLE (
        VALUES ( Data[Company] ),
        FILTER ( ALL ( Data ), 'Data'[Code] = 100 )
    )
RETURN
    CALCULATE ( SUM ( 'Data'[Hours] ), ALL ( Data ), 'Data'[Company] IN _table )

      And the final output is as below:


      Best Regards,
      Dino Tao
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.