Forum Discussion

cottrera's avatar
cottrera
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

DAX SUM based on multiple criteria

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
ActivityTime_Mins
HOLIDAY300
SICK200
BANK_HOL400
WORKING40000
COLLECTION2000
TRAVEL15000
DOCTORS120
FLEET800
TRAINING1200
DEPOT100

 

Here are the results I would exspect

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

 

thank you

RIchard

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
    Anonymous
    6 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      cottrera
      Icon for Post Prodigy rankPost Prodigy
      6 years ago

      Works fine thanks you your quick response

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.