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
PhilTo1 , You can create measure for this
First create a new measure for total salary
TotalSalary = CALCULATE(SUM('YourTable'[Value]), ALLEXCEPT('YourTable', 'YourTable'[Employee number]))
Then for percentage
PercentageSplit = DIVIDE(SUM('YourTable'[Value]), [TotalSalary], 0)
In your report, add a table visual.
Add the Employee Number, Staff Name, Cost Code, and Period fields to the table.
Add the Value field to show the salary values.
Add the PercentageSplit measure to show the percentage splits.
Hi, thanks for the superquick response. This almost worked but the % split was a total of the cost across all periods, not by period.
