Help with percentage DAX formula

[mork]

Hello all,

I have a table with some resource data. Among that data I have the tree following columns.

"Weekly capacity per resource in hours", Weekly project work + Administrative work per resource in hours" and "Weekly project work per resource in hours".

How can I have either using a measure or a calculated column a line diagram that has the above three columns in percentages?

I want the capacity column to be always 100%, and the other two columns in percentage according the capacity %.

For examle If a resource's capacity for a week is 40 hours I want these 40hours to be shown as 100% and if the project work of the same resource for the same week is 30 hours I want to see the percentage of that work according to the 100% of the capacity.

Also if for a week tha capacity is 32 hours (maybe because there were 4 work days that week) I want the capacity still be 100% and if the resource for that week had a project work of 30 hours I want to see the percentage of that work according to the 100% of the capacity which is 32 hours.

[kcantor]

I would not use a column. I would use measures as they take up less file size and are quicker to load. For this, I recommend Divide instead of / to account for errors.

Capacity = DIVIDE([Weekly Capacity Per Resource in Hours], [Weekly Capacity Per Resource in Hours])

Project work + Admin = DIVIDE([Weekly project work + Administrative work per resource in hours], [Weekly Capacity Per Resource in Hours])

Project work = DIVIDE([Weekly project work per resource in hours], [Weekly Capacity Per Resource in Hours])

Once you enter these, be sure to set the data type as percent.

This will be done in the ribbon above the measure calculation. I prefer Divide because it will account for errors of dividing by zero without giving you a #NUM error.

[mork]

@kcantor

I tried what you said and I get the following error:

"a single value for column "Capacity" in table "Resources" cannot be determined. This can happen when a measure formula refers to a column tha contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

How can I fix that?

[kcantor]

Sorry, I though you already had calculations in place to sum up those numbers. You would need to create a sum of the columns first using Measure =SUM('Table Name'[Column name]) and us the name of these new measures in the measures I posted. Then, make sure before you use the measure that you have a table, matrix, or graph set up with the axis already set. Without the original aggregation, it was trying to do that on each row.

My bad . . .

[mork]

@kcantor But I don't want to sum this numbers.

In my table each row corresponds to one week. So basically I have the capacity, and work for that week. and I want these numbers in percentage for each week. That way I can create a visualization with week number in the x axis and the capacity and work percentages in the y axis.

[kcantor · Accepted solution]

I understand that. You have to  Sum them in order to use them in measures. When you break it back out into the report on the axis or by rows it will only sum that particular week and not add any others in unless they have the same week number associated by the date table. Even if you only have 1 row it will sum, either by you adding it or when you select the data to use. this is due to the portablility of the measures which will allow you to reuse for each week.