I've created a matrix table calculated from 3 tables and a Date Table. These tables are TimeRecord, EmployeeSalary and InternalExpense. The datasoureces are MS excel.

1. TimeRecored -> This table contains employee time tracking for each task, contains Column ID,Name, Date, Task, ProjectCode,Time(decimal).
2. EmployeeSalary -> This table record salary of each employee in each month.
3. InternalExpense -> This table record expenses with company in each month.

After created for ID and Date relationship in those model. I've create some measure to summary each Project Cost by Employee's salary x the ratio of time each employee spend on each project and this time ratio also calculate with total monthly expense to distribute this cost in the projects cost. Here are my measures.

1. TimeRatio = DIVIDE(
                       SUM('TimeRecord'[Time]),
                       CALCULATE(SUM('TimeRecord'[Time]),
                                ALL('TimeTracking'[Project])
                                )
                          )

2. SalaryXWorkRatio = ([WorkingRatio]*SELECTEDVALUE(Salary[Salary]))
3. ALLExpense = SUM(Expense[Cost])
4. ProjectExpense = [AllExpense]*[WorkingRatio]

SumSalaryWorkRatio = SUMX(VALUES(Clockify[Project]),
       CALCULATE(SUMX(VALUES(Clockify[Code]),[SalaryXWorkRatio])))

6. ProjectTotalCost = [SumSalaryWorkRatio]+[ProjectExpense]

Here's my expected result. enter image description here

But its work for some months, when i filter cost in Jan, Mar, May, Aug, Oct. The Expense column not show any value in rows but show correct value in total. like this. while the Total cost column missing calculation with expense cost too. enter image description here

What should i do? I guess it was my measure that not specific some condition in detail. But I still don't understand why the value missing in some months. Could you please suggest me if i need to revised my measure, change it or check anything . Thank you.

I've created a matrix table calculated from 3 tables and a Date Table. These tables are TimeRecord, EmployeeSalary and InternalExpense. The datasoureces are MS excel.

1. TimeRecored -> This table contains employee time tracking for each task, contains Column ID,Name, Date, Task, ProjectCode,Time(decimal).
2. EmployeeSalary -> This table record salary of each employee in each month.
3. InternalExpense -> This table record expenses with company in each month.

After created for ID and Date relationship in those model. I've create some measure to summary each Project Cost by Employee's salary x the ratio of time each employee spend on each project and this time ratio also calculate with total monthly expense to distribute this cost in the projects cost. Here are my measures.

1. TimeRatio = DIVIDE(
                       SUM('TimeRecord'[Time]),
                       CALCULATE(SUM('TimeRecord'[Time]),
                                ALL('TimeTracking'[Project])
                                )
                          )

2. SalaryXWorkRatio = ([WorkingRatio]*SELECTEDVALUE(Salary[Salary]))
3. ALLExpense = SUM(Expense[Cost])
4. ProjectExpense = [AllExpense]*[WorkingRatio]

SumSalaryWorkRatio = SUMX(VALUES(Clockify[Project]),
       CALCULATE(SUMX(VALUES(Clockify[Code]),[SalaryXWorkRatio])))

6. ProjectTotalCost = [SumSalaryWorkRatio]+[ProjectExpense]

Here's my expected result. enter image description here

But its work for some months, when i filter cost in Jan, Mar, May, Aug, Oct. The Expense column not show any value in rows but show correct value in total. like this. while the Total cost column missing calculation with expense cost too. enter image description here

What should i do? I guess it was my measure that not specific some condition in detail. But I still don't understand why the value missing in some months. Could you please suggest me if i need to revised my measure, change it or check anything . Thank you.

• powerbi
• dax
• powerbi-desktop

1 Answer 1

I already fixed this problem. It's due to my tables relationship between TimeRecord[Date] and Datetable[Date] (Many to one). This relationship cross-filter direction was "Single". So i managed relationship in the tables and change this to "Both-way". Then my table back to normal as i expected.