Forum Discussion
Helper VHelp with percentage DAX formula
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.
Community ChampionI 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.
Helper VI 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
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.