Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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.
Solved! Go to 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
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:
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:
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!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
65 | |
63 | |
52 | |
37 | |
36 |
User | Count |
---|---|
82 | |
66 | |
61 | |
46 | |
45 |