I'm trying to show the amount of days a company travel has taken. The sales employee doesn't enter the exact amount so it is calculated in SQL and in the data load to DWH it is added to the TravelID. If travelID exists 3 times due to 3 trip lines connected to it, each line gets the amount of 3.
DWH Database
TravelID 1234 TriplineID 2345 #Days 3
TravelID 1234 TriplineID 2346 #Days 3
TravelID 1234 TriplineID 2347 #Days 3
So one TravelID can have multiple TriplineID's
The problem is when i want to make my cube measure, is that it counts up all the 3's. In this case it shows me #Days is 9 for one travel, while it must be 3:
#Days
--TravelID 1234
--TripLineID -2345 3
--TripLineID -2346 3
--TripLineID -2347 3
Total 9
I then thought i'd devide it by a Count of the rows.
([Measures].[# Days] / [Measures].[Count])
Then it shows me a partly correct result:
#Days
--TravelID 1234
--TripLineID -2345 3
--TripLineID -2346 3
--TripLineID -2347 3
Total 3
Total 2
Since there obviously are multiple TravelID's, it also devides that Total, while there it must actually make the sum of the previous Totals.
What would be an efficient way to implement this? I spend a whole day cracking my head over this but i can't seem to find a good solution.
Here is a bigger picture of the problem:
#Days
--TravelID 1234
--TripLineID -2345 3
--TripLineID -2346 3
--TripLineID -2347 3
Total 3
--TravelID 1235
--TripLineID -2348 4
--TripLineID -2349 4
--TripLineID -2350 4
Total 4
Total 2
Where the 2 here should have been 7.
The only solution i have found up to now, is to create a second fact table. I was trying to avoid this but i didn't see any other way.