Forum Discussion

matkvaid's avatar
matkvaid
Icon for Helper III rankHelper III
4 years ago
Solved

Getting SUM by other column relationship

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's avatar
    tamerj1
    4 years ago

    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