Forum Discussion
Difficult Sum Measure
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])) )
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.
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
)
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.