Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThe 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHey @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |