Forum Discussion
Percentage Calculation
I would like to add a card to my report to show the percentage difference between different resource types
- Resource Type is the column name (within a table called Utilisation)
- Project Manager (OS) is a type within the column and the total for Jan was 40
- Project Manager (TPS) is a type within the column and the total for Jan 20
- How do I show a card with 50% on (indicating that we have 50% more of Project Manager (OS) than we do Project Manager (TPS))
Hi nicolcutts
Based on my understanding, [hours] in Greg_Deckler's formula is the same field as "Count of Resource Type" in your example.
If "Count of Resource Type" is a measure, you need to use "SUMX' function.
Measure1 = SUMX(FILTER(ALL(Sheet7),Sheet7[Resource Type]="Project Manager (OS)"),[Count of Resource Type]) Measure2 = SUMX(FILTER(ALL(Sheet7),Sheet7[Resource Type]="Project Manager (TPS)"),[Count of Resource Type]) Measure3 = ([Measure1]-[Measure2])/[Measure1]
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Really depends on your data and how you are presenting it. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
That being said, you could create measures like:
OSHours = SUMX(FILTER('Utilisation',[Resource Type]="Project Manager (OS)"),[Hours]) TPSHours = SUMX(FILTER('Utilisation',[Resource Type]="Project Manager (TPS)"),[Hours]) Percent = [TPSHours] / [OSHours]Make sure you get those TPS reports in on time. Did you get the memo?
Utilisation Table
Resource Type Count of Resource Type
Project Manager (OS) 40
Project Manager (TPS) 20
The table looks like the above. I understand the formula;
1. Work out the total for Project Manager (OS) in the resource type field
2. Work out the total for Project Manager (TPS in the resource type field
3. Work out the percentage
I dont understand sorry the ,[Hours] part is this a field in the table or just a field to return the value to
OSHours = SUMX(FILTER('Utilisation',[Resource Type]="Project Manager (OS)"),[Hours])
Hi nicolcutts
Based on my understanding, [hours] in Greg_Deckler's formula is the same field as "Count of Resource Type" in your example.
If "Count of Resource Type" is a measure, you need to use "SUMX' function.
Measure1 = SUMX(FILTER(ALL(Sheet7),Sheet7[Resource Type]="Project Manager (OS)"),[Count of Resource Type]) Measure2 = SUMX(FILTER(ALL(Sheet7),Sheet7[Resource Type]="Project Manager (TPS)"),[Count of Resource Type]) Measure3 = ([Measure1]-[Measure2])/[Measure1]
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
