ABC client analysis using Data from 3 different tables

[MK_TT]

Hi all,

I am interested in creating a ABC analysis on clients. I have 5 tables in total, see below.

All tables are linked using ID codes.

For the classification, I am interested in using columns from the tables- Client_data_2, Accounting1 and Claims2.

The column "Client Code" from the Client_data_2 table would be used to identify EACH clients' classification based on the Sum values of the "BPREMS TOTAL" column from the Accounting1 table AND the sum of the"GROSS_PAID" column of the Claims2 column. 

What is the best approach for this?

[v-kkf-msft]

Hi @MK_TT ,

I am not sure if I understood your question correctly. Generally, we recommend minimizing the use of bi-directional relationships. They can negatively impact on model query performance. 

And we don't recommend relating two fact-type tables directly using many-to-many cardinality. The main reason is because the model won't provide flexibility in the ways you report visuals filter or group. Instead of relating fact-type tables directly, we recommend you adopt Star Schema design principles. You do it by adding dimension-type tables. The dimension-type tables then relate to the fact-type tables by using one-to-many relationships. It lets you filter or group using any of the dimension-type columns, and summarize any related fact-type table.

Do you have all these tables building relationships through [Client Code] field? If so, please use star schema, create a dimension table 'Clients', and establish the following relationships.

For more information, please refer to: 

Understand star schema and the importance for Power BI 

Many-to-many relationship guidance 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.