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
rbrechet
Helper I
Helper I

Mapping one account with two sales rep

Hello dear community.

 

I am facing the below challenge:

I am trying to map salesforce territory with my sales data in order to follow up sales representative performance.

This is normally simple but not if two sales representative share one account (one has 40% sales the other 60%).
Do someone has any clue about how to create formula to link sales % split with sales data ?

 

Many thanksexemple

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Alright, so you make a new column in fact table called rep key. The DAX formula looks like this:

 

Repkey = 'Fact1 Table'[Code]&" "&'Fact1 Table'[Sales rep]

 

You make a similar column in your rep table:

 

Repkey = 'Rep Table'[code]&" "&'Rep Table'[Sales rep]

 

Then you create a relationship between fact table and rep table with these columns.

 

Then you create a calculated column with this DAX formula:

 

Total amount = 'Fact1 Table'[Amount] * RELATED('Rep Table'[Split])

 

Let me know how it goes. I'll gladly elaborate.

View solution in original post

16 REPLIES 16
Anonymous
Not applicable

Hi @rbrechet

 

 

I come with a solution.

 

Try this DAX formula:

 

Sales pr. rep = SUMX(SUMMARIZE('Sales rep';'Sales rep'[Split]; 'Sales rep'[code];'Fact'[Amount]);'Sales rep'[Split]*'Fact'[Amount])

 

I didn't use your table 2. I simply made a connection between table 1 and 3 by using the account code.

 

Let me know if it works out.

 

Best,

Martin

Many thanks @Anonymous,

 

Working on it ... but in my dax formula I do not have option to go for Fact'[Amount]) I am only offered to choie within table 1 ....

 

I am probably doing smth wrong

Anonymous
Not applicable

@rbrechet

 

Try making the sales pr. rep in your table 1. I called table 1 for fact and table 3 for sales rep. I'll gladly elaborate.

Hummm

 

I tried, still do not offer me choice.
I assume this is because I keep using table 2 for between table 1 and 3

Indeed in table 1 since for each accounts I have many lines of "product" I cannot do direct connection (duplicate lines).

 

Again @AnonymousI most be thanksful for you answer and patience

Anonymous
Not applicable

@rbrechet

 

Sorry, I misspelled. What you do is, you forget your table 2. Then you create a connection between table 1 and table 3. Then you create a measure in table 1 with the DAX code, which I gave you. Then you should be good to go. Let me know if you insist on having table 2. Then we'll figure something out together. 

 

Let me know how it goes. I also gladly elaborate if you want me to 🙂

 

Don't forget to give kudos and accept a solution, if it works out for you. 

@Anonymous

 

I do not insist for table 2, fact is just I cannot do relation bewteeen table1/3 because of duplicate account code on both (one because of multiple sales rep per account, another one for multiple products per accounts) ....

Anonymous
Not applicable

@rbrechet

 

I've been experimenting with different possible solutions, and I want to ask you; Do you have any control over the database, and how the data is stored? 

 

The reason I'm asking is because I have specific solution in mind. The idea would be to have an index column on table 1 and then use the index column as an id column, since, as far as I understand, this table has one row for one order. The idea would then be to duplicate all the all rows for code 30. You can relate these to columns based on the sales rep name (or some id that you decide on).

 

Let me know if it makes sense.

 

See example below:

 

possible solution.png

Many thanks, sound good idea.

 

I do have control over database.

 

The way it is done:

=> Since no data warehouse possible, I have daily night data extration from software to csv files.

=> My PBI is link to thoses files and been update daily (files are been merged into PBI)

 

If it helps

 

Anonymous
Not applicable

I understand. Is it possible to implement my suggestion about creating an Id column and duplicating the orders attached to account code 30?

Sure, I will implement it.

 Once done,  and connected, we will need to go back with your dax formula I assume

Anonymous
Not applicable

@rbrechet

 

Yes naturally. Just message me here 🙂

Done 🙂

Anonymous
Not applicable

Alright, so you make a new column in fact table called rep key. The DAX formula looks like this:

 

Repkey = 'Fact1 Table'[Code]&" "&'Fact1 Table'[Sales rep]

 

You make a similar column in your rep table:

 

Repkey = 'Rep Table'[code]&" "&'Rep Table'[Sales rep]

 

Then you create a relationship between fact table and rep table with these columns.

 

Then you create a calculated column with this DAX formula:

 

Total amount = 'Fact1 Table'[Amount] * RELATED('Rep Table'[Split])

 

Let me know how it goes. I'll gladly elaborate.

Yes it works this way !!!


Many thanks, I will deal with it  now, espcially if multiples product/date lines per accounts.

 

Thanks for patience and solution 🙂 🙂

Anonymous
Not applicable

@rbrechet

 

No problem, always feel free to contact me if you have any kind of trouble with Power BI 🙂

rbrechet
Helper I
Helper I

Capture.JPG

 

 

 

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors