I want to calculate the total salery amount based on days worked and the daily salery.
The model should also allow me to filter by location, salery type and month.
The issue I meet is that I don't have the location in the days worked table. I only know how many days they have worked. So when create a table it will not filter out Lisa who has not worked at Building 1. What is the best way of solving this?
Is it possible to create a measure that allows me to calculate the totalt salery correctly or maybe create one fact table with the days worked, and still be able to filter by location and salery type?
Allocate the Days to a location on basis of the weighted average of Daily Salary per Location. So EMP number 1 will have the Days allocated for 99.9% to location 1 and for 0.1% to location 2. Make a new table with these allocations, disconnect the old Days table and connect the new one to the dimension tables (including Locations).