Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Aapuitdemouw
Frequent Visitor

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

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.

 

Aapuitdemouw_0-1649145474413.png

 

1 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

View solution in original post

6 REPLIES 6
johnt75
Super User
Super User

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

Hi, thanks for replying!

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

Aapuitdemouw_0-1649231582503.png

 

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

 

You're missing a ) after the first ]

Ah, that was a bit dumb, my bad.

 

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

 

Aapuitdemouw_1-1649235275333.png

 

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

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.