Forum Discussion

nicolcutts's avatar
nicolcutts
Frequent Visitor
7 years ago
Solved

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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

nicolcutts's avatar
nicolcutts
Frequent Visitor
7 years ago

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])

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

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.