Creating a new table with missing data and calculation over different tables

[SamGeurts]

I am new to PowerBI, but  I have been looking around and trying to find out before spamming you. So in advance, sorry  if I am  asking the potential obvious.

I have 2 tables:  one for "Registered customers" and another one with sales from "Buying customers". I often end up with customers which were not registered but with sales, or registered customers with no sales. I need to check how on track I am with the expected sales and  take actions where needed.

My inputs tables

Registered customers	Expected value
Customer1	20
Customer2	30
Customer3	40

Buying customers	Sales
Customer 1	10
Customer 1	10
Customer 1	10
Customer 1	10
customer 2	20
customer 2	20
customer 100	100
customer 100	100

I would like to generate a new table with all customers (Union + Distinct worked) as first colum, have a colomn "sales" with the sum of sales of each customer and another coulmn with the expected value, as is. The new table would look like this

New Table	Sales	Expected Value
Customer 1	40	20
customer 2	40	30
customer 3	"No sales"	40
customer 100	200	"Anomaly?"

I tried creating relationship between my tables and without creating relationship, none worked so far. Appreciate any support/hint 🙂

[Wilson_ · Accepted solution]

Hey Sam,

I've attached a sample pbix to solve your problem.

The key thing I did was to create a customer table that has a relationship with both tables. (You can right-click the Customers table and go to Edit query to see how I created that table directly from your data.)

If you don't already know the terms dimension table and fact table, this wonderful course from SQLBI will be a great foundational learning for you. The bad news is your problem was indeed quite simple. However, the great news is your problem was very simple; you can have pretty easily learn the tools to solve this if you want!

Please feel free to ask followup questions and I will be happy to answer them. 😄

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[SamGeurts]

Thank you. That what happens when you try to be effective with a tool within one day 🙂 
 

[Wilson_]

You're welcome. I get it. I've been there! 😄