Forum Discussion
% split with multiple criteria
Hi Phil,
If you wanted the second table to have the same layout as the one you showed (I'm guessing you used a Matrix visual?) you could add a measure which divides the salary for each employee and cost code by the total salary for the employee. Here's a basic version of a measure which would do that:
Salary as % of Employee Total Salary = DIVIDE( SUM(YourTable[Salary]), CALCULATE( SUM(YourTable[Salary]), REMOVEFILTERS(YourTable[Cost Code]) ) )You can format the measure as a percentage using the Measure Tools tab in the ribbon:
And when you create the matrix you'll see results similar to this:
N.B. I used the following mock data:
Staff Name Period Cost Code Salary Employee 1 Period 1 Cost Code 1 8000 Employee 1 Period 1 Cost Code 2 2000 Employee 2 Period 1 Cost Code 1 6000 Employee 2 Period 1 Cost Code 2 4000 Employee 1 Period 2 Cost Code 1 9000 Employee 1 Period 2 Cost Code 2 1000 Employee 2 Period 2 Cost Code 1 5000 Employee 2 Period 2 Cost Code 2 5000 Employee 1 Period 3 Cost Code 1 7500 Employee 1 Period 3 Cost Code 2 2500 Employee 2 Period 3 Cost Code 1 6500 Employee 2 Period 3 Cost Code 3 3500
Hi Phil,
If you wanted the second table to have the same layout as the one you showed (I'm guessing you used a Matrix visual?) you could add a measure which divides the salary for each employee and cost code by the total salary for the employee. Here's a basic version of a measure which would do that:
Salary as % of Employee Total Salary = DIVIDE(
SUM(YourTable[Salary]),
CALCULATE(
SUM(YourTable[Salary]),
REMOVEFILTERS(YourTable[Cost Code])
)
)
You can format the measure as a percentage using the Measure Tools tab in the ribbon:
And when you create the matrix you'll see results similar to this:
N.B. I used the following mock data:
| Staff Name | Period | Cost Code | Salary |
| Employee 1 | Period 1 | Cost Code 1 | 8000 |
| Employee 1 | Period 1 | Cost Code 2 | 2000 |
| Employee 2 | Period 1 | Cost Code 1 | 6000 |
| Employee 2 | Period 1 | Cost Code 2 | 4000 |
| Employee 1 | Period 2 | Cost Code 1 | 9000 |
| Employee 1 | Period 2 | Cost Code 2 | 1000 |
| Employee 2 | Period 2 | Cost Code 1 | 5000 |
| Employee 2 | Period 2 | Cost Code 2 | 5000 |
| Employee 1 | Period 3 | Cost Code 1 | 7500 |
| Employee 1 | Period 3 | Cost Code 2 | 2500 |
| Employee 2 | Period 3 | Cost Code 1 | 6500 |
| Employee 2 | Period 3 | Cost Code 3 | 3500 |
Thankyou so much, this worked perfectly
Great, happy that it helped!
