Help: calculation of matrix table with no subtotal or with other value

[marabeda]

Dear Community, I need to create a matrix table where I show 3 columns:

1) Cadenza H: is how many hours at that day each cleaner has to do

2) Durata H: is the real time that the cleaner worked

3) Tempo H: is the time after correction

I show the table matrix filter by client, date, cleaner

The problem is that the subtotal per date is double.

Here the relationship table

Know I have to find a way to divide cadenza H by the number of cleaner (dipendenti) in the matrix table and sumup.

I hope to have correctly explained I am not an expert

Can Anyone help?

Thank you

[tamerj1]

Hi @marabeda 

your data model is ambiguous. The filter comming comming from bridge giamini is moving to one dependenti though two paths, one is direct and the other through cadenzi - bridge clienti - dependenti

[marabeda]

I rationalise better all the table. Probably shoud work. What you think? Is it achivable now my matrix table? Thank you

[tamerj1]

@marabeda 

Much better now but as @Whitewater100  suggested it is better to have a calendar table in order to avoid slicing by fact table columns. 

[marabeda]

Thank you Tamerj1, but if I add a Date table do I have to related with 'Ore dipendenti or leave without relation?

Does it make a change in my problem?

Thank you again.

[marabeda]

HI, thank you for the reply at the beginning my data model was composed by 2 tables.

1) ore dipendenti

2) cadenze

But the problem is that both were linked by a relation many to many. I tried to use a bridge table. The only one that i found was cliente/mandato. I do not really need a Date table.  The aims is to check if the cleaner went to the client the day (cadenze[giorno]) = ore dipendenti[data entrata] and to check if the cleaner respect the assigned time cadenze[cadenze H] =ore dipendenti[durata H].

I can also cancel Bridge giorni, I think is useless.

I hope that explanation is usefull

Thank you for any help

[marabeda]

I rationalise better the structure.It should be better.

[Whitewater100]

Hi:

Are you missing your date table? The date field in your matrix should come from that. I'm also wondering why the bridge tables unless you are tracking clients and cleaners? Your model may need a litttle work due to bi-di relationships too. Usually your fact(data table) is on the many-side and yours is on the one side. Just a few observations..

Thanks..

[marabeda]

Hi thank, so at the begging there were 2 tables ore dipendenti and cadenze. The relationship was many to many: many clients can have many days, where the cleaner go to clean the offices. So I try to read around and create a bridge table with cliente/mandato. Then I start to build the data table even if I do not really need. The aims is to check if the cleaner respect the days where he goes to clean ore dipendenti[data entrata]=cadenze[giorno] and the planified time cadenze[Cadenza H]=ore dipendenti[DurataH].

Can this explanation help?

[marabeda]

I rationalise better the structure:

Fact table is ore dipendenti and Cadenze than I added Dim table as I want the table matrix.

Is it better?

Thx