Forum Discussion
Helper IMapping one account with two sales rep
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.
Helper IMany 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
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
Hummm
I tried, still do not offer me choice.
I assume this is because I keep using table 2 for between table 1 and 3Indeed 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
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.
- rbrechet
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) ....