Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
stvesanity
Frequent Visitor

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" 

 

ActivityHours
Running1
Swimming2
Jumping3
Rest2
Dancing6

 

TIA

1 ACCEPTED SOLUTION

@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.
 

View solution in original post

4 REPLIES 4
PaulDBrown
Community Champion
Community Champion

@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]

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

@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.
 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.