Forum Discussion
Dividing Columns Using Unique ID
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.
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
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.
7 Replies
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/1447523Table 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!
Hi powerboss1
In your first tables, you have multi rows with the same "id", how do you want to deal with it?
