Difficult Sum Measure

[PAschieri]

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.

The difficult part is that the result should ignore the company filter as below:

Thanks!

[Bibiano_Geraldo · Accepted solution]

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

[Bibiano_Geraldo]

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]

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.

[v-junyant-msft · Accepted solution]

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

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

[jdbuchanan71]

@PAschieri 

Based on your description "If the company has activities > 0 for code 100, then SUM all hours for that company." shouldn't the measure amount for Company B = 0?

2 measures

1 to sum hours

Total Hours = SUM(YourTable[Hours])

1 to calculate the total hours for the filtered list

MEASURE = 
VAR _100Companies =
    FILTER (
        VALUES ( YourTable[Company] ),
        CALCULATE ( SUM ( YourTable[Activities] ), ALLEXCEPT ( YourTable, YourTable[Company] ), YourTable[Code] = "100" ) > 0
    )
RETURN
    CALCULATE (
        SUMX ( ALLEXCEPT ( YourTable, YourTable[Company] ), [Total Hours] ),
        _100Companies
    )

[PAschieri]

Hi, thanks for your answer but it´s not correct.  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.