counting similar fields in different tables

[cengizeralp]

Hi experts,

I have a problem about calculation of a ratio for service units.

I have two tables, one for assembly of the products ("Izlenebilirlik") and one for service calls of these products ("Servis_Fişleri"). This two table is connected with product serial number as you can see from the picture below.

"ServisAdı_" and "Mon_Servisadı" comes from the same table and so has the same service company name dimension.

.

The trouble is that one product can be assembled and repaired (if there is) by different service companies. And I would like to find a process ratio for every service company without restriction of the relation.

Nominater = How many repair were done by service compnay A : Count(ServisAdı_) 

Denominater= How many assembly were done by service compnay A :Count(Mon_Servisadı)

Can anyone help on this?

Thanks in advance.

[cengizeralp]

Thank you for your suggestion.

It solved my problem but changed completely my key field behaviour . The relation for many serial numbers have broken.

I am using this structure for many other purposes. So I need a solution without changing key field structure.

[zebhashmi]

Thank you - I think you have to charge Your Data Modle, A in Assy and A in Repair are two different identities.

I tried Using P() but no we need some relation to that. Maybe some can help Thank you!

[Digvijay_Singh · Accepted solution]

I think this is better, no change in existing model but you need one extra island table  as below -

ServiceName:

Load Distinct Assembly_Service_Name as Service_Name

Resident Assemblies;

Concatenate

Load Distinct Repair_Service_Name as Service_Name

Resident Repairs Where Not exists(Service_Name,Repair_Service_Name);

[cengizeralp]

Hi Digvijay,

That's the solution I am looking for.

Thank you very much.

Cengiz