Two Unrelated Tables

[Anonymous]

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

[PaulDBrown · Accepted solution]

@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]))

[Giorgi1989]

Thank you immensely! This worked like a charm for my case as well! 

[SteveCampbell]

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 tables

I would reccomend reading this, too:
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

[Anonymous]

Both of you are great! Thanks a ton.

[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!

[Anonymous]

Surely i will follow @SteveCampbell suggestion and build dimension tables. Thanks!