Forum Discussion

rbrechet's avatar
rbrechet
Icon for Helper I rankHelper I
9 years ago
Solved

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...
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    9 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

  • rbrechet's avatar
    rbrechet
    Icon for Helper I rankHelper I
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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.

      • rbrechet's avatar
        rbrechet
        Icon for Helper I rankHelper I
        9 years ago

        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