Dividing Columns Using Unique ID

[powerboss1]

Hello, I am attempting to divide two columns from two different tables using a unique ID. I do not want to sum both columns then divide. I am trying to divide "Total Square Footage by Territory"  by "Total by Territory" using "Territory" as the unique ID.

[Ritaf1983 · Accepted solution]

Unlike Excel, Power BI's engine "thinks" columns, not cells.

Therefore, the only option I can see to achieve your goal is to add an index column at the territory level.
Next, create an identification column based on the concatenation between the index and the territory.

If you need a full demonstration, please attach 2 tables in a usable format

(Not a picture)

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ritaf1983]

Hi @powerboss1  

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[powerboss1]

Table 1
Great Lakes Territory	76367.65
Mid Atlantic Territory	78102
Table 2
Great Lakes Territory	675.84
Mid Atlantic Territory	750.46
Final Solution
Avg Head Count by Territory
Great Lakes Territory	112.9966412
Mid Atlantic Territory	104.0721691

Here is the sample data as well as the final solution that I am looking for. I'm not sure this calculation is possible, but it would save a lot of time if it is! Refer to the tables in the original post to understand how the data is coming in. Thanks!

[Ritaf1983]

Hi @powerboss1 
In your first tables, you have multi rows with the same "id", how do you want to deal with it?

[powerboss1]

If it's possible to just have duplicates:

Great Lakes Territory	112.9966412
Great Lakes Territory	112.9966412
Mid Atlantic Territory	104.0721691
Mid Atlantic Territory	104.0721691

I hope this makes sense!

[johnyip · Accepted solution]

Assuming your table with Total Square Footage by Territory is 'Data 1', and another table is 'Data 2', you can create a calculated column in 'Data 2' as follows:

Answer = LOOKUPVALUE('Data 1'[Total Square Footage by Territory],'Data 1'[Territory],'Data 2'[Territory]) /'Data 2'[Total by Territory]

And as @Ritaf1983 suggests, you should provide sample data in usable format in case your question is complicated that people need to actually work for it for the answer. Without the data structure and/or table name you may not able to get the answer as this can be crucial to the solution.

[johnyip]

Please mark my answer as solution if this solves your issue, so others with similar questions can look for this thread.

[Ritaf1983 · Accepted solution]

Unlike Excel, Power BI's engine "thinks" columns, not cells.

Therefore, the only option I can see to achieve your goal is to add an index column at the territory level.
Next, create an identification column based on the concatenation between the index and the territory.

If you need a full demonstration, please attach 2 tables in a usable format

(Not a picture)

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly