Forum Discussion

Salvetti_01's avatar
Salvetti_01
Regular Visitor
1 year ago
Solved

Payroll Grouping Question

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 wo...
Need Help
  • Salvetti_01's avatar
    Salvetti_01
    1 year ago

    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
        )
    )
Salvetti_01's avatar
Salvetti_01
Regular Visitor
1 year ago

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)

 

    • Salvetti_01's avatar
      Salvetti_01
      Regular Visitor
      1 year ago

      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
          )
      )