Forum Discussion
Dynamic percentage based on attributes applied
I'd like to create a measure that displays a percentage of time-phased labour cost data dependent on the attributes added to a visual. The attributes I want to be able to apply are (I want the ability to apply any number of these attributes to see, for example, things like the percentage of time/cost a given team has spent on a project):
- Project Name
- Resource Name
- Resource Role
- Resource Team Name
- Month & Year
I grouped the data in the table via the Query Editor and added a Total Cost column so the Actual Cost value can be divided by Total Cost to display a percentage, but am stuck on the best measure to create for this.
Here is a sample of the type of data I'm working with and how it's been grouped:
| ResourceName | ProjectName | TeamName | Role | Month & Year | Start of Month | SumActualCost | Total Cost |
| Resource I | Example Project | Team T | Role O | Jan-2018 | 1/01/2018 | 5165 | 137480.1 |
| Resource A | Example Project | Team T | Role O | Nov-2017 | 1/11/2017 | 5162.5 | 137480.1 |
| Resource BB | Example Project | Team C | Role N | Jun-2017 | 1/06/2017 | 5075 | 137480.1 |
| Resource V | Example Project | Team T | Role L | Dec-2017 | 1/12/2017 | 5075 | 137480.1 |
| Resource V | Example Project | Team T | Role L | Dec-2016 | 1/12/2016 | 5025 | 137480.1 |
| Resource V | Example Project | Team T | Role L | May-2016 | 1/05/2016 | 4975 | 137480.1 |
| Resource F | Example Project | Team T | Role M | Dec-2016 | 1/12/2016 | 4975 | 137480.1 |
| Resource V | Example Project | Team T | Role L | Sep-2016 | 1/09/2016 | 4950 | 137480.1 |
| Resource I | Example Project | Team T | Role O | Apr-2017 | 1/04/2017 | 4915 | 137480.1 |
| Resource O | Example Project | Team D | Role D | Aug-2016 | 1/08/2016 | 4875 | 137480.1 |
| Resource A | Example Project | Team T | Role O | Sep-2017 | 1/09/2017 | 4837.5 | 137480.1 |
| Resource I | Example Project | Team T | Role O | Feb-2016 | 1/02/2016 | 4835 | 137480.1 |
| Cost Resource B | Example Project | Role C | Jul-2017 | 1/07/2017 | 4827.611483 | 137480.1 | |
| Resource C | Example Project | Team M | Role O | Jun-2017 | 1/06/2017 | 4825 | 137480.1 |
| Resource F | Example Project | Team T | Role M | Jan-2018 | 1/01/2018 | 4800 | 137480.1 |
| Resource A | Example Project | Team T | Role O | Jun-2017 | 1/06/2017 | 4762.5 | 137480.1 |
| Resource V | Example Project | Team T | Role L | Jul-2016 | 1/07/2016 | 4725 | 137480.1 |
| Resource F | Example Project | Team T | Role M | Dec-2017 | 1/12/2017 | 4725 | 137480.1 |
| Resource F | Example Project | Team T | Role M | Aug-2017 | 1/08/2017 | 4700 | 137480.1 |
| Resource R | Example Project | Role F | Apr-2016 | 1/04/2016 | 4625 | 137480.1 | |
| Resource F | Example Project | Team T | Role M | Jan-2017 | 1/01/2017 | 4500 | 137480.1 |
| Resource BB | Example Project | Team C | Role N | Aug-2017 | 1/08/2017 | 4450 | 137480.1 |
| Resource E | Example Project | Team T | Role M | Mar-2016 | 1/03/2016 | 4425 | 137480.1 |
| Resource S | Example Project | Team A | Role K | Oct-2016 | 1/10/2016 | 4400 | 137480.1 |
| Resource DD | Example Project | Team T | Role A | Sep-2017 | 1/09/2017 | 4400 | 137480.1 |
| Resource E | Example Project | Team T | Role M | Feb-2018 | 1/02/2018 | 4400 | 137480.1 |
| Resource A | Example Project | Team T | Role O | Mar-2017 | 1/03/2017 | 4375 | 137480.1 |
| Resource F | Example Project | Team T | Role M | Apr-2017 | 1/04/2017 | 4350 | 137480.1 |
| Resource V | Example Project | Team T | Role L | Mar-2016 | 1/03/2016 | 4325 | 137480.1 |
Ok great, the formatting must have been the elusive element.
Doing some quick maths:
208764.42 / 37968039.17 = 0.005498425. As a percentage that should be 0.54%. So that means you are correct in that its 100 times too small, plus the number itself is wrong.
Lets create 2 more measures.Numerator = SUM('YourTable'[SumActualCost]) Denominator = SUM('YourTable'[Total Cost])Bring those into the matix next. I'm curious what that comes up with.
For the reason the reason it doesn't add to 100%, i believe My error is with Total Cost. Instead of SUM for the Denominator, we should use MAX instead. This is because you don't actually want to sum up that column.