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.
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:
Helper IMany 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
- rbrechet
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 :) :)
No problem, always feel free to contact me if you have any kind of trouble with Power BI :)
I understand. Is it possible to implement my suggestion about creating an Id column and duplicating the orders attached to account code 30?
- rbrechet
Sure, I will implement it.
Once done, and connected, we will need to go back with your dax formula I assume
- rbrechet
Done :)
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.