Forum Discussion
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.
The difficult part is that the result should ignore the company filter as below:
Thanks!
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.
6 Replies
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.
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.
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.
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])) )
