% split with multiple criteria

[PhilTo1]

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 

[AndrewGould · Accepted solution]

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]

Thankyou so much, this worked perfectly

[AndrewGould]

Great, happy that it helped!

[bhanu_gautam]

@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.

[PhilTo1]

Hi, thanks for the superquick response. This almost worked but the % split was a total of the cost across all periods, not by period.