Need to compare 2 columns that come from different tables

[kdeff6006]

I need to find the difference between 2 columns in a table, and the 2 columns come from different tables. 

As you can see the table is comparing sales reps' total bookings vs what they're quotas were per quarter. Then at then end there is a total column. I would like to add a column for each quarter that does Bookings - Quotas to show the difference between the two. Bookings comes from it's own table and Quotas comes from another. Is this possible to do? If I cannot do it by quarter can I take the difference of Booking - Quotas in the totals column at the end?

[Eric_Zhang · Accepted solution]

@kdeff6006

If your tables are in a relationship as below, you can just create a measure as

difference = SUM(bookings[Booking])-SUM(quota[Quota])

See attached demo.

[kdeff6006]

@Eric_Zhang Thank you for your response. Doing the calculated measure worked. However, when I add the "Difference" measue to my visualization, I lose some filters that I had applied and I see more data than I would like to. Have you ever seen this issue?

[Eric_Zhang]

---

@kdeff6006 wrote:

@Eric_Zhang Thank you for your response. Doing the calculated measure worked. However, when I add the "Difference" measue to my visualization, I lose some filters that I had applied and I see more data than I would like to. Have you ever seen this issue?

---

@kdeff6006

What are the filters? Could you post a sample pbix file? You can upload it to Onedrive or any web storage and share the link. Do mask sensitive data before uploading.

[kdeff6006]

@Eric_Zhang I actually solved my problem. Thank you very much!

Is there anyway to do advanced conditional formatting? Right now I'm only seeing the gradient option. I want to have the cells filled green if the difference is 0 or greater and red if it is below 0. I don't see that as an option currently.

[Eric_Zhang]

---

@kdeff6006 wrote:

@Eric_Zhang I actually solved my problem. Thank you very much!

Is there anyway to do advanced conditional formatting? Right now I'm only seeing the gradient option. I want to have the cells filled green if the difference is 0 or greater and red if it is below 0. I don't see that as an option currently.

---

@kdeff6006

You can apply "color by rules".

[kdeff6006]

@Eric_Zhang I cannot find that feature. All I see is the gradient option

[Eric_Zhang]

---

@kdeff6006 wrote:

@Eric_Zhang I cannot find that feature. All I see is the gradient option

---

@kdeff6006

Try to download the latest Power BI desktop.

[kdeff6006]

@Eric_Zhang Thanks for your help!

[Pcees-123]

@Eric_Zhang ,

I have two tables order and in-hand quantity. These 2 tables are related. Order table has order # and ordered item and ordered qty.  In-hand table has  quantity stored for ordered item. I need to calculate difference between ordered and in-hand quantity columns from these 2 tables. Also I need to check if in-hand quantity is more then result should be 0 else difference between those columns. How can I achieve this?