Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am producing the table below, but can't work out how to calculate the percentage of a subset, but not of the "Total" data.
What I mean is there are three Categories of data, Cat A, Cat B and Cat C. Cat A contains a number of Task Categories, as shown in the table below, and within each Task Category there are a number of tasks. I want to show the percentage booked to each Task (per week), but only as a percentage of the Total of Cat A tasks. I need to exclude any tasks that are Cat B or Cat C completely from the calculation. This is what I have so far, but the percentages are calculating based on all the data (including Cat B and Cat C), not just Cat A. Any help will be greatly appreciated.
=if(WklyDate>=today()-30 and WklyDate<=today(),
if([RTB Task Category]='Incident Management',
(num(sum({$<[RTB Task Category]={'Incident Management'}>}[WklyTimebookings])/
sum({$<[WTProjType]={'Cat A'}>}all [WklyTimebookings]),'#,##0.0%')),
if([RTB Task Category]='Management',
(num(sum({$<[RTB Task Category]={'Management'}>}[WklyTimebookings])/
sum({$<[WTProjType]={'Cat A'}>}all [WklyTimebookings]),'#,##0.0%')),
if([RTB Task Category]='Operations',
(num(sum({$<[RTB Task Category]={'Operations'}>}[WklyTimebookings])/
sum({$<[WTProjType]={'Cat A'}>}all [WklyTimebookings]),'#,##0.0%')),
(num(sum({$<[RTB Task Category]={''}>}[WklyTimebookings])/
sum({$<[WTProjType]={'Cat A'}>}all [WklyTimebookings]),'#,##0.0%')),
))))
Week Commencing | 02/05/2022 | 02/05/2022 | 09/05/2022 | 09/05/2022 | 16/05/2022 | 16/05/2022 | |
Task Category | Task Name | Hours Booked | % | Hours Booked | % | Hours Booked | % |
Incident Management | Incident management (Critical, High, Medium) | 142 | 0.01% | 134 | 0.01% | 146 | 0.01% |
Incident Management | Incident management (Low) | 663 | 0.06% | 681 | 0.06% | 511 | 0.04% |
Incident Management | Problem management - root cause analysis (Critical, High, Medium) | 169 | 0.01% | 115 | 0.01% | 130 | 0.01% |
Incident Management | Problem management - root cause analysis (Low) | 246 | 0.02% | 273 | 0.02% | 150 | 0.01% |
Incident Management | Total | 1,219 | 0.10% | 1,203 | 0.10% | 937 | 0.08% |
Management | Risk and control remediation | 213 | 0.02% | 197 | 0.02% | 186 | 0.02% |
Management | Service Governance | 46 | 0.00% | 62 | 0.01% | 18 | 0.00% |
Management | Stakeholder Management | 26 | 0.00% | 32 | 0.00% | 20 | 0.00% |
Management | Team Management - Performance Management | 16 | 0.00% | 27 | 0.00% | 16 | 0.00% |
Management | Team Management - RTB Meetings | 181 | 0.02% | 231 | 0.02% | 134 | 0.01% |
Management | Team Management - Support / Admin | 104 | 0.01% | 115 | 0.01% | 82 | 0.01% |
Management | Total | 586 | 0.05% | 665 | 0.06% | 456 | 0.04% |
Operations | Ad-hoc Data Reports | 118 | 0.01% | 117 | 0.01% | 97 | 0.01% |
Operations | Ad-hoc Service | 196 | 0.02% | 159 | 0.01% | 103 | 0.01% |
Operations | Capacity/Performance Management | 28 | 0.00% | 45 | 0.00% | 33 | 0.00% |
Operations | Creation and Review of Service management/ Production MI metrics | 99 | 0.01% | 87 | 0.01% | 77 | 0.01% |
Operations | Disaster Recovery/Business Continuity Planning - Failover planning and testing | 33 | 0.00% | 48 | 0.00% | 18 | 0.00% |
Operations | Knowledge management and documentation maintenance | 557 | 0.05% | 555 | 0.05% | 452 | 0.04% |
Operations | Production Data Updates/loads/patching | 210 | 0.02% | 138 | 0.01% | 102 | 0.01% |
Operations | Service Monitoring and Responding to Alerts | 1,161 | 0.10% | 1,151 | 0.10% | 983 | 0.08% |
Operations | Start of Day/End of Day/Production Readiness/Health checks | 95 | 0.01% | 106 | 0.01% | 72 | 0.01% |
Operations | User communications & support - App/User Training | 25 | 0.00% | 23 | 0.00% | 6 | 0.00% |
Operations | User communications & support - Password Management | 4 | 0.00% | 17 | 0.00% | 16 | 0.00% |
Operations | User communications & support - Service Request Management | 253 | 0.02% | 267 | 0.02% | 200 | 0.02% |
Operations | User communications & support - User Queries | 653 | 0.05% | 652 | 0.05% | 711 | 0.06% |
Operations | Total | 3,430 | 0.29% | 3,365 | 0.28% | 2,869 | 0.24% |
- | Asset and configuration management | - | - | 6 | 0.00% | 4 | 0.00% |
- | Automation and Optimisation | 28 | 0.00% | 26 | 0.00% | 28 | 0.00% |
- | Management | 8 | 0.00% | 11 | 0.00% | 11 | 0.00% |
- | Non Warranty Bug Fix - Deployment | 6 | 0.00% | - | - | - | - |
- | Non Warranty Bug Fix - Development | 7 | 0.00% | 8 | 0.00% | - | - |
- | Production Change Scheduling, Release Review, Co-ordination and Air Traffic control | 7 | 0.00% | 4 | 0.00% | 9 | 0.00% |
- | Regulatory Requirements | - | - | - | - | 4 | 0.00% |
- | Total | 56 | 0.00% | 55 | 0.00% | 56 | 0.00% |
Total | 5,292 | 0.00% | 5,287 | 0.00% | 4,317 | 0.00% |
Hi, instead of 'all' try using 'total':
=if(WklyDate>=today()-30 and WklyDate<=today(),
if([RTB Task Category]='Incident Management',
(num(sum({$<[RTB Task Category]={'Incident Management'}>}[WklyTimebookings])/
sum({$<[WTProjType]={'Cat A'}>}total[WklyTimebookings]),'#,##0.0%')),
You can also try to set an expresson with just sum({$<[WTProjType]={'Cat A'}>}total[WklyTimebookings]) and check the expression until it returns the value you want, once you have the correct expression for the divisor you can use t in the expression.