Forum Discussion
Two Unrelated Tables
Hi Guys,
I need help with below tables:
Objective: To calculate Inquiry/Qty from two different unrelated tables. Below are the tables. Entity is my Dummy company name, Qty is sales qty, Inquiry is # of issues we got. I am looking to calculate Inquiry/Qty.
Is there a way we can do this?
| Entity | Qty |
| A | 2 |
| B | 1 |
| C | 4 |
| D | 2 |
| A | 5 |
| G | 3 |
| G | 1 |
| E | 2 |
| B | 4 |
| D | 5 |
| C | 3 |
| ENTITY | INQUIRY |
| A | 1 |
| B | 1 |
| C | 1 |
| D | 1 |
| A | 1 |
| G | 1 |
| G | 1 |
| E | 1 |
| B | 1 |
| D | 1 |
| C | 1 |
Thanks
Rohit
Anonymous
I would recommend the solution suggested by SteveCampbell , but if you don't want to create a Dimension Table, you could try:
Inquiry divided by Qty =
CALCULATE( DIVIDE(SUM(table2[ [Inquiry]), SUM(table1 [Qty])), TREATAS(VALUES(table1[Entity]), table2[Entity]))
6 Replies
PaulDBrownCommunity Champion
Anonymous
I would recommend the solution suggested by SteveCampbell , but if you don't want to create a Dimension Table, you could try:
Inquiry divided by Qty =
CALCULATE( DIVIDE(SUM(table2[ [Inquiry]), SUM(table1 [Qty])), TREATAS(VALUES(table1[Entity]), table2[Entity]))
Giorgi1989Advocate II
Thank you immensely! This worked like a charm for my case as well!
- SteveCampbell
Memorable Member
Yes, builfd a dimension table that has all the uniqe list of company names.
I would advise to do this in Power Query if possible. Otherwise if one of the tables has all companies, you can use
VALUES(TABLE[Entity])
Then you can join this two your two fact tablesI would reccomend reading this, too:
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema- PaulDBrown
Anonymous
As much as I appreciate you marking my suggestion (which in reality is only a "plan b" compared to building a dimension table) as a solution, I still strongly recommend you follow SteveCampbell suggestion. It will make life much easier!
