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.
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])
)
)
SalaryXWorkRatio = ([WorkingRatio]*SELECTEDVALUE(Salary[Salary]))
ALLExpense = SUM(Expense[Cost])
ProjectExpense = [AllExpense]*[WorkingRatio]
SumSalaryWorkRatio = SUMX(VALUES(Clockify[Project]),
CALCULATE(SUMX(VALUES(Clockify[Code]),[SalaryXWorkRatio])))
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 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.