What I currently have
I currently have a table like so:
| Index | Type | Type (Groups) | Units | Personnel |
| 0 | ABC | 1 | 0.5 | Person A |
| 1 | DEF | 2 | 1.1 | Person B |
| 2 | GHI | 3 | 2 | Person A |
| 3 | JKL | 4 | 31 | Person C |
| 4 | DEF - a | 2 | 25 | Person D |
| 5 | a - JKL | 4 | 56 | Contractor A |
| 6 | DEF - ab | 2 | 0.8 | Contractor B |
| 7 | GHI - b | 3 | 12 | Contractor C |
| 8 | GHI - b | 3 | 0.2 | Person C |
| 9 | GHI | 3 | 111 | Person D |
| 10 | JKL | 4 | 33 | Person A |
| 11 | ft - DEF | 2 | 62 | Person B |
| 12 | GHI - x | 3 | 18 | Person A |
| 13 | ABC - a | 1 | 1 | Person C |
| 14 | JKL - bc | 4 | 0.3 | Person A |
| 15 | JKL | 4 | 0.47 | Person B |
| 16 | DEF | 2 | 0.25 | Person A |
| 17 | DEF | 2 | 0.17 | Person A |
| 18 | GHI | 3 | 3 | Person B |
| 19 | JkL | 4 | 4 | Contractor B |
| 20 | ABC | 1 | 11 | Person B |
| 21 | DEF - zy | 2 | 100 | Contractor C |
| 22 | DEF - zy | 2 | 2.5 | Person B |
| 23 | ABC - ab | 1 | 7.5 | Contractor B |
| 24 | GHi | 3 | 3.8 | Person C |
| 25 | DEF - s | 2 | 8.1 | Contractor B |
| 26 | ax - ABC | 1 | 28 | Person A |
| 27 | ABC - es | 1 | 97 | Contractor A |
The index was created so I could do the following measure:
1. Total Group 1 Measure =
var tab =
ADDCOLUMNS(
'Table',
"Result",
IF(
Table'[Type (groups)]="1",
CALCULATE(
SUM('Table'[Units]),
Table'[Index]=EARLIER('Table'[Index])
)
)
)
return
SUMX(
tab,
[Result]
)
I can create the relevant calcs I need from this to sum units based on specific groups.
What I want to achieve
The problem I now have is that I want to create a new adjustment to this calculation slightly so that it does not include the any Personnel which has "Contractor" in the name so basically "Contractor A", "Contractor B" and "Contractor C".
The end goal is to be able to then total up the figures from this new measure and divide it by the totals of the first measures.
Hope this all makes sense.
Thank you.
