I have multiple columns containing numeric data which I would like to sum them all up in a measure.
Output would be a measure that sums all my specified columns.
Columns: STUDY LEAVE hours, Annual Leave hours, Unpaid Leave hours etc.
1 Answer
You can simply do:
Total Leave Hours = SUM('Table'[Study Leave Hours]) + SUM('Table'[Annual Leave Hours]) + SUM('Table'[Unpaid Leave Hours])
Or, do a measure for each of the types of hours above (such as Total Study Leave Hours = SUM('Table'[Study Leave Hours])), and then add the 3 individual measures together:
Total Leave Hours = [Total Study Leave Hours] + [Total Annual Leave Hours] + [Total Unpaid Leave Hours]
5 Comments
Rush
Thanks @Leonard. That worked well. The only issue I now have is to Sum a column grouped by 2 columns which I will use a measure to subtract from. My end goal would be to calculate utilization: Sum of Billable Hours / (Available Hours in month) - Sum of time-off hours) which I would filter down by Business Unit & Practice.
Leonard
How do you mean "grouped by 2 columns"? Measures aren't columns, so you can do some very powerful tricks with them (using CALCULATE for example). I'd recommend working on each component measure independently & then combining them into a 4th measure. Do ask if you have a question about a specific measure (sample tables/data are always helpful too)
Rush
I managed to sort it out. Thanks Leonard. I needed to group the data then do calculations on it which worked out.
Data Monger
@Leonard, For Step 1 how do you account for Blank values?
Leonard
For a SUM like this question, BLANK is treated like a 0, so you likely don't need to do anything. If BLANK has a value other than 0, or if you're doing divisions or multiplications, you'd want to test for blanks using ISBLANK, and then handle them in a way that makes sense for your specific situation.