Forum Discussion

stvesanity's avatar
stvesanity
Frequent Visitor
5 years ago
Solved

How to not include

Hello,   Need your expertise, please. If i have this set of activity, how will i come up with a measure that will    1.show total of all activity except "rest" for example 2. total of "rest hrs"...
Need Help
  • stvesanity's avatar
    stvesanity
    5 years ago

    PaulDBrown 

     

    please disregard my initial reply... here's what i've got after following what was instructed

     

    1) Sum of hours = SUM(Table[Hours]) - OKAY

    2) Sum of rest hours = CALCULATE([Sum of Hours], Table [Activity] = "Rest") - OKAY

    3) Sum of all excep "Rest" = 
    CALCUALATE([Sum of hours], ALL(Table[activity])) - [Sum of rest hours] - 

    Measure = CALCULATE([Total Forecasted Hours],ALL('Consolidated File'[Data.Project Task]-[Total Avail time]))

    error = 
    A single value for column 'Data.Project Task' in table 'Consolidated File' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
     
PaulDBrown's avatar
PaulDBrown
Community Champion
5 years ago

stvesanity 

With the following measures:

1) Sum of hours = SUM(Table[Hours])

2) Sum of rest hours = CALCULATE([Sum of Hours], Table [Activity] = "Rest")

3) Sum of all excep "Rest" = 
CALCUALATE([Sum of hours], ALL(Table[activity])) - [Sum of rest hours]

 

  • stvesanity's avatar
    stvesanity
    Frequent Visitor
    5 years ago

    Appreciate your quick revert PaulDBrown

     

    here's what i got

     

    Avail Hours = CALCULATE(SUM('Consolidated File'[Value],ALL('Consolidated File'[Data.Project Task]-SUM('Consolidated File'[Value]))))
     
    error:
    Too many arguments were passed to the SUM function. The maximum argument count for the function is 1.
  • stvesanity's avatar
    stvesanity
    Frequent Visitor
    5 years ago

    PaulDBrown 

     

    please disregard my initial reply... here's what i've got after following what was instructed

     

    1) Sum of hours = SUM(Table[Hours]) - OKAY

    2) Sum of rest hours = CALCULATE([Sum of Hours], Table [Activity] = "Rest") - OKAY

    3) Sum of all excep "Rest" = 
    CALCUALATE([Sum of hours], ALL(Table[activity])) - [Sum of rest hours] - 

    Measure = CALCULATE([Total Forecasted Hours],ALL('Consolidated File'[Data.Project Task]-[Total Avail time]))

    error = 
    A single value for column 'Data.Project Task' in table 'Consolidated File' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
     
  • stvesanity's avatar
    stvesanity
    Frequent Visitor
    5 years ago

    PaulDBrown it's all fixed!!! amen! thank you sir! i learned new stuff today!