DAX SUM based on multiple criteria

[cottrera]

Hi 

I have a table called Activity which contains activity types and time associated.  I need to perform a sum based on 7 of these activity types

Table_Activity
Activity	Time_Mins
HOLIDAY	300
SICK	200
BANK_HOL	400
WORKING	40000
COLLECTION	2000
TRAVEL	15000
DOCTORS	120
FLEET	800
TRAINING	1200
DEPOT	100

Here are the results I would exspect

SUM (HOLIDAY,SICK,BANK_HOL,DOCTORS,TRAINING,DEPOT) =3120

thank you

RIchard

[Anonymous · Accepted solution]

You can use the following measure for this:

Measure =
CALCULATE (
    SUM ( 'Table'[Time_Mins] );
    'Table'[Activity] <> "WORKING"
        && 'Table'[Activity] <> "COLLECTION"
)

Kind regards
Joren Venema

Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily. 

[Anonymous · Accepted solution]

@cottrera  - 

You could also add a Calculated Column to differentiate different groupings:

Category = 
IF(
   'Table'[Activity] IN {"HOLIDAY","SICK","BANK_HOL","DOCTORS","TRAINING","DEPOT"}, "In", "Out"
)

 Whether you use a grouping or not, you'll probably want to use a Slicer visualization:

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[Anonymous · Accepted solution]

You can use the following measure for this:

Measure =
CALCULATE (
    SUM ( 'Table'[Time_Mins] );
    'Table'[Activity] <> "WORKING"
        && 'Table'[Activity] <> "COLLECTION"
)

Kind regards
Joren Venema

Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily. 

[cottrera]

Works fine thanks you your quick response