Incorrect Sum of Columns and Rows in Matrix

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,

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