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