Getting SUM by other column relationship

[matkvaid]

Hello, please help - i am trying to find a solution to calculate driver worked hours by car in this situation:

My fact table is Routing Sheet, where driven car on that day saves (most of them are more than once per day - can be two or more routes). I take worked days with distinct count of date rows and result is good. But i want to calculate worked hours wich are stored in work schedule - "Tabelis", for each day and each driver. Wen i put everyting i get total worked hours for each car, to calculate per car total expenses (now if car was driven by two drivers in a month i get double salary). I added userelationship between tables, but i think i am missing something. Here red is same driver, different cars. 

[tamerj1 · Accepted solution]

Hi @matkvaid 

https://www.dropbox.com/t/028rB5MRFHkzjDsl

I understand the problem. Actually there is nothing in your data model that tells the Power Bi engine which hours spent by which driver on which car. No matter how your play with the relationships, as long as this information is missing there in no way to split the hours over cars. For example if the schedule table includes the car number then everything will work smoothly becuase it will become very clear to engine which car driven by the employer at a given date/time. (I applied the solution but it turned out that drivers can actually drive more than one car in a single day. Therefore it is not correct yet I will post it anyway) 
We might be able to find a way out, for example if the driver can drive only one car in a specific date then it should be possible. We can create a cross-join table between fact and schedule in order to complete the schedule with car numbers

Complete Schedule = 
SELECTCOLUMNS (
    FILTER ( 
        CROSSJOIN ( Schedule, DISTINCT ('Fact' ) ),
        [Factual Time Of Departure] = [Data]
            && [Shipping Agent Employee Code] = [Darbuotojo Nr_]
    ),
    "FA_No_", [FA No_],
    "Shipping Agent Employee Code", [Shipping Agent Employee Code],
    "Valandu skaicius", [Valandu skaicius],
    "Date", [Data]
)

Then use this table instead in the model

[matkvaid]

Hello again and thank You very much! That is fully enough of solution. There is very rare situation that drivers would change cars between days, normaly it is fully enough just to calculate one day worked hours like here with driven car. If there was two drivers this could be training, etc - so for now it is completely enough. I can have correct driven hours from GPS software, but this will not correlate with workschedule as this is full work day and cant be related to driven car in our database. So for my calculation this will work. Thank You again 🙂

[tamerj1]

@matkvaid 
That's great!
It is also worth it to double cjeck your schedule data. I noticed the work hour are missing for at least one day (26 Mrch) which reflected a difference in the work days between my report and your original one. Hours and day must be computed out of the same table

[matkvaid]

I am cheking all data ant its structures, this is my first bigger model, it has few fact tables and i still learn to model data, because our structure has multiple tables for info, like in this example fact really has 'fact header' -> 'fact lines' and then all real sales values are per item in items value entry table... 🙂

[tamerj1]

Hi @matkvaid 

can you share sample file with dummy names and data?

[matkvaid]

Hi, attaching data - tried summarize, lookupvalue, but i am doing something wrong here... 

https://easyupload.io/slciht