Payroll Grouping Question

[Salvetti_01]

Sample Payroll Grouping Excel

Hi everyone,

I could use some help with a report I'm trying to build to group employee salaries by specific groups. The link above is a sample data model I'm working with. Any help would be greatly appreciated!

Problem:
I need to reconcile payroll reports across multiple group cost centers. Employees can either be homed in a specific group or external to our groups and assigned to another department within the organization. The challenge I'm having is with employees homed in one group and paid by another.

I'm wondering what the best approach is to solve this (Power Query or DAX)?

Payroll_Actual is composed of two reports:
Payroll by fund: displays all employees charging one of the group funds, whether or not they are homed in the group.
Payroll by Home Cost Center: displays all salary sources for employees homed in a group, regardless of whether the funding comes a group fund or another department.

Desired Report:
I would like to create a report that can be filtered by group and provides:
All salaries charged to a specific group fund (PD_Fund).
All employees homed in the group (PD_HCC) but charging salaries to other department funds.
When the filter is removed, I don't want to double count those homed in one group and charging another group.

Current Report:
The current report works when the slicer is All, Apple, or Orange.
When group is Home Group, I don't see all the groups (Apple and Orange are filtered out)

[Salvetti_01]

Thanks @Ashish_Mathur for letting me know about the file access. Here is the link to an Excel file. I'm working on the PBI file to share as well.  Below are the tables of my data model and the report I currently have.

This table shows my current report and directly below shows what I'm hoping to receive when filtered by Home Group.

Actual_Payroll table

Groups Table

Data Model - Groups(Group) - Actual_Payroll(Group)

PD_Fund table (not part of data model)

PD_HCC (not part of data model)

[Ashish_Mathur]

Hi,

I have solved a similar problem in the attached file.

Hope this helps.

[Salvetti_01 · Accepted solution]

Thanks Ashish. Disconnecting the tables was helpful. In addition, some DAX was needed.

Earning Amount =

VAR SelectedGroup = SELECTEDVALUE(Groups[Group])

VAR SelectedCostCenter = LOOKUPVALUE(Groups[Group Cost Center],Groups[Group], SelectedGroup)

RETURN

CALCULATE(

    SUM('Actual_Payroll'[Earnings Amount]),

    FILTER(

        Actual_Payroll,

        Actual_Payroll[Charged Cost Center] = SelectedCostCenter

        || Actual_Payroll[Home Cost Center] = SelectedCostCenter

    )

)

[Ashish_Mathur]

Hi,

Access Denied message.  In that PBi file, cclearly show the problem and the expected result.