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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.