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.

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.

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.