Incorrect Sum of Columns and Rows in Matrix

[jhobzvel]

I have 3 simple tables to show the example of my issue. There were no formulas or measures here.

Employee - List of employees and their position

Salary - Monthly salary of employees and it increases monthly

Department - Department of employees listed monthly since they may be transferred

Date Table

My goal is to have a total Salary per Department and Employee like below and I am almost there but as you can see, the row and column totals were wrong which is why I posted here. There seems to be a problem with relationship and I can't pinpoint where.

Example: Anita is part of Sales in January, IT in Ferbruary and Marketing in March. So the salary total of Anita under IT should be 75 and not 225. 

Also, each departments column total is the same like 350 for January, 525 for Feb and 700 for March which are incorrect.

Relationship is like this:

Employee table:

Salary table:

Department Table:

[DataInsights · Accepted solution]

@jhobzvel,

Try this measure:

Total Salary =
CALCULATE (
    SUM ( Salary[Salary] ),
    CROSSFILTER ( Department[Employee], Employee[Employee], BOTH ),
    CROSSFILTER ( Department[Month], 'Date'[Date], BOTH )
)

The reason you need the CROSSFILTER function is that the relationships are unidirectional, meaning that filters can't flow from Department to Employee, for example. This is the correct way to design a data model. Some developers use bidirectional relationships to achieve this, but numerous issues can result. Always use unidirectional relationships.

[DataInsights · Accepted solution]

@jhobzvel,

Try this measure:

Total Salary =
CALCULATE (
    SUM ( Salary[Salary] ),
    CROSSFILTER ( Department[Employee], Employee[Employee], BOTH ),
    CROSSFILTER ( Department[Month], 'Date'[Date], BOTH )
)

The reason you need the CROSSFILTER function is that the relationships are unidirectional, meaning that filters can't flow from Department to Employee, for example. This is the correct way to design a data model. Some developers use bidirectional relationships to achieve this, but numerous issues can result. Always use unidirectional relationships.

[jhobzvel]

I got an error like this when I created the measure and added it in the matrix.

[DataInsights · Accepted solution]

@jhobzvel,

Try the solution shown below. The Department table is a Type 2 Slowly Changing Dimension that tracks the association of Employee and Department over time. The concept is to pull the relevant Department into the fact table, and exclude the Department table from the star schema.

1. In the Salary table, create a calculated column:

Department = 
LOOKUPVALUE (
    Department[Department],
    Department[Employee], Salary[Employee],
    Department[Month], Salary[Month]
)

2. Remove relationships with the Department table:

3. Create measure:

Total Salary = SUM ( Salary[Salary] )

4. Create matrix using fields from the Date, Employee, and Salary tables:

Here's a great video on the topic:

https://www.youtube.com/watch?v=tKeaQpWynzg 

[jhobzvel]

It fixed the issue I am facing and you save so much of my time! Thank you so much @DataInsights.

I appreaciate your help and your time in checking this!