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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dee
Helper III
Helper III

Find The dIfference betwen invoices and quotes

Hi all,

 

I currently have two tables transactions and quotations.

 

The transactions has the invoices while quotations has the quotes.

 

I am trying to find whether the customer who requested a quote finalized with a invoice. Any help with how I can work it out below are my tables samples.

 

PS// only a specific branch gives out quotes so let's say the BranchEntityid = '12345xxx'

 

Quotes Table

[TransactionKey]    [TransactionEntityID] [BranchEntityID] [CustomerEntityID] [TransactionCode] [TransactionDate]

2000xxxxxxxxxxx     12345xxxxx               6789xxxxx           abcdxxxxxxxxx        Querty890             2019/05/17 

 

[TransactionKey]    [TransactionEntityID] [BranchEntityID] [CustomerEntityID] [TransactionCode] [TransactionDate]

2000xxxxxxxxxxx     12345xxxxx               6789xxxxx           abcdxxxxxxxxx         INVerty890             2019/05/17 

 

The end goal is to find the difference in numbers between the two tables.

 

TIA

7 REPLIES 7
MFelix
Super User
Super User

Hi @Dee ,

 

You need to relate both table by unique ID's then you just need to make the count of both tables and you will get the information you need.

 

How do you relate a quote with the Invoice? What is the unique field or fields that allow you to make that connection?

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



The unique key is the transactionentityid 

Hi @Dee ,

 

Believe that you don't have unique values on the table so you need to create a dimension table with unique values to relate both and then calculate the count of both tables.

 

You example of data is pretty simple can you share additional information as example and expected result so I can share a more detailed explanation?

 

Thank you.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi Felix,

 

You've lost me a little, how do I link up the two tables, that's all the data I got from the two tables.

Hi @Dee ,

 

I'm assuming that not all of the KEY are in both tables because you have orders without invoice and probably some invoices that are not in the orders is this correct?

 

You need to create a third table to make the connection. As I refered can you share a little bit more detail of both tables (can be mockup data)

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hey @MFelix 

 

Since the transactions seem to be unique between the two tables, can I use it to make a temporary table to connect the two?, I've tried joining them but it ends up overwriting the invoice column(Transaction code).

 

I am not sure what you mean by me providing more data, as those are the similar columns between the two tables, maybe you could elaborate a little bit more n what I could add.

 

Sorry for the trouble and thank you.

Hi @Dee ,

 

I have a few questions since you only show a line for both tables:

 

How is your data setup?

Can you expand a little bit more your example?

What is the table that as always values quotes or invoices?

Are there any quotes without invoices or vice versa?

 

If you can share a sample file would be great.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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