Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Samiozt
Frequent Visitor

Managing Relationship - Person Allocation Territories

Hello,

I am using 2 tables.
1. Table "Data" comes regularly every month.
2. Table "Person" changes every quarter.
Staff shares territories. For example, 5/5 of the 10 boxes sold share. I can't do power bi. Can you help me with Table Association? Thanks.

 

Person Table 

GRUPTERRITORIESPERSONSHARE
REDISTANBUL11R01-A.VELI1
REDANKARA11R01-A.VELI0,5
REDIZMIR11R01-A.VELI1
REDBURSA11R01-A.VELI0,5
REDANKARA11R02-O.BARAN0,5
REDADANA11R02-O.BARAN1
REDBURSA11R02-O.BARAN0,5

 

Data Table

TERRITORIESMARKETPRODUCTSMonthUNITS REPORT
ISTANBULBIDO1.02.202236
ISTANBULAMID1.02.202238
ISTANBULAIME1.02.20227
ISTANBULBGEL1.02.202265
ISTANBULBIDO1.01.202221
ISTANBULAMID1.01.202224
ISTANBULAIME1.01.202210
ISTANBULBGEL1.01.202230
ANKARABIDO1.02.202220
ANKARAAMID1.02.20224
ANKARAAIME1.02.20221
ANKARABGEL1.02.202211
ANKARABIDO1.01.202230
ANKARAAMID1.01.20221
ANKARAAIME1.01.20221
ANKARABGEL1.01.202212
IZMIRBIDO1.02.20223
IZMIRAMID1.02.20224
IZMIRAIME1.02.20220
IZMIRBGEL1.02.202216
IZMIRBIDO1.01.20227
IZMIRAMID1.01.20222
IZMIRAIME1.01.20223
IZMIRBGEL1.01.202211
ADANABIDO1.02.202214
ADANAAMID1.02.202233
ADANAAIME1.02.202228
ADANABGEL1.02.202214
ADANABIDO1.01.202231
ADANAAMID1.01.202223
ADANAAIME1.01.202218
ADANABGEL1.01.202215
BURSABIDO1.02.202210
BURSAAMID1.02.20224
BURSAAIME1.02.20223
BURSABGEL1.02.202225
BURSABIDO1.01.202211
BURSAAMID1.01.20223
BURSAAIME1.01.20222
BURSABGEL1.01.202219

 

Power Bi Relation Ship

Samiozt_0-1647947966892.png

 

Samiozt_3-1647948499011.png

 

 

 

1 ACCEPTED SOLUTION

Hey @Samiozt , 

So the only way I can see this working is if you merge together Person and Data together into the Data table. 

Watsky_0-1648562666093.png

Expand to include GRUP, PERSON, and SHARE.

Watsky_1-1648562687544.png

Then create a custom called RESULT by multiplying UNITS REPORT * SHARE

Watsky_2-1648562720273.png

Then add in the RESULT column 

Watsky_4-1648562949162.png

 


Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up!
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

Proud to be a Super User!

View solution in original post

5 REPLIES 5
Watsky
Solution Sage
Solution Sage

Hi @Samiozt,

So you have two tables that have a key with duplicate values. So, you need a joining table between them that has all unique values from each table in one column. To do this first go into Power Query (Transform Data) Highlight Territories then Right click on one of the tables and select Add as New Query. Do this to both tables.

Watsky_0-1647956634198.png

Now you have two lists called Territories and Territories (2). 

On each list click on it then go to Transform then To Table. There will be a popups asking about delimiters we can just click ok. Now, we have two (2) tables.

On the Territories table click append queries (if you do the drop-down then click Append Queries and not Append Queries as New).

Watsky_1-1647956992942.png

Select the Territories (2) table.

Watsky_2-1647957087031.png

Now we have all the values in one table and one column. 

Watsky_4-1647957165238.png

Click Remove Rows then Remove Duplicates.

Watsky_3-1647957156447.png

This gives a unique list of values.

Right click on Territories (2)  and uncheck the Enable Load option.

Watsky_6-1647957261983.png

On the Territories table rename the column to TERRITORIES and change the data type to text. Click Close and Apply. 

On the Model screen you will likely see that Power BI has joined the three tables together. If not then join Data to Territories and Territories to Person.

Change the Cross filter direction of the Person to Territories join to Both. 

Watsky_7-1647957520617.png

Create your Matrix the same way you created it originally except this time use the Territories from the Territories table.

Watsky_8-1647957642654.png

This should give you the results you are looking for.

Watsky_9-1647957668690.png

Hope this helps and if you're looking for a different result then let me know what result you're looking for.


Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up!
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

Proud to be a Super User!

Hello @Watsky, thank you for the reply. I am sending the desired result as excel. (Expected Values).
Power Bi result The area I marked as red is wrong.
I'm having trouble as my issue is multiplying Shares by sums in these top totals.

 

Samiozt_0-1648023583809.png

 

 

Hello again everyone;
You can see the excel and pbix link where it reaches the correct result.

Share Demo Link

Hope you can help.

Hey @Samiozt , 

So the only way I can see this working is if you merge together Person and Data together into the Data table. 

Watsky_0-1648562666093.png

Expand to include GRUP, PERSON, and SHARE.

Watsky_1-1648562687544.png

Then create a custom called RESULT by multiplying UNITS REPORT * SHARE

Watsky_2-1648562720273.png

Then add in the RESULT column 

Watsky_4-1648562949162.png

 


Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up!
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

Proud to be a Super User!

It worked thank you very much. 🙏👍

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors