Forum Discussion
% split with multiple criteria
Hi,
I'm a fairly new user to Power BI, apologies if there is a simple answer to this!
I'm working on a payroll report which looks something like
Period 1 Period 2 Period 3 etc.
Employee number Staff Name 1 Cost Code 1 Value Value Value
Cost Code 2 Value Value Value
Employee number Staff Name 2 Cost Code 1
Cost Code 2
This part of the report runs absolutely fine but I want to include another table showing the % salary splits. So for example if staff member 1 had a salary of 10,000 split with 8,000 on 1 code and 2,000 on another it would show as 80% and 20%.
The solutions I've seen are all fairly limited in that the filters have been specified by name in the body of the formula but I have hundreds of staff members and cost codes so this simply isn't feasible.
Any help would be massively appreciated!
Thanks in advance
Phil
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
5 Replies
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.
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!
