Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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)
Solved! Go to Solution.
Thanks Ashish. Disconnecting the tables was helpful. In addition, some DAX was needed.
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)
Thanks Ashish. Disconnecting the tables was helpful. In addition, some DAX was needed.
Hi,
Access Denied message. In that PBi file, cclearly show the problem and the expected result.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
142 | |
71 | |
64 | |
52 | |
50 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |