Have two columns from different datasets interact with eachother (perform calculation)

[Aapuitdemouw]

Hi,

I would like some help with the following. I have two different imported datasets both with a column named ''Customer Nr''.

The first data set is a list of customer complaints of the current year, so with the possibility that a certain customer has more than 1 complaint and thus is mentioned more than once in the list. The second data set has the totale deliveries for all potential customers in this year, so again the possibility to be mentioned more than once in the list.

What I want to do is show the ratio of amount of complaints per customer as compared to their total deliveries. My question is; how to set up a certain calculation where there more possibilities of mentioned values per list? I think I should create a new calculated column or measure but not quite sure which formula to use.

[johnt75]

You can create a new table with all the customer numbers with

Customers = DISTINCT( UNION( VALUES( Complaints[Customer number]), VALUES( Orders[Customer number]) ) )

then link this new table to both your existing tables in one-to-many relationships.

You can then create measures like

Ratio complaints to deliveries = DIVIDE( COUNTROWS( Complaints), COUNTROWS(Orders))

and put that in a visual with the customer number from the new table

[Aapuitdemouw]

Hi, thanks for replying!

When I try the first step I get the following error message:

PowerBI won't let me link this new table to the existing tables, before fixing this issue. Any idea what I'm doing wrong?

Kind regards

[johnt75]

You're missing a ) after the first ]

[Aapuitdemouw]

Ah, that was a bit dumb, my bad.

However, now I am facing the following issue of a circular dependency:

[johnt75 · Accepted solution]

Ah, I think you might need to do it in Power Query then. If you create 2 new queries by referencing your existing queries, you can delete all the columns except customer number, append the queries and then remove duplicate values. you should then be able to link that new table with the other 2

[Aapuitdemouw]

Ah, I think I managed to make it work. Thanks for explaining!