Forum Discussion
splitting sales $
hi
I have not been able to find an answer to how to take the sales $ from Sales rep A and split them evenly between 2 reps
for example
Rep A has sales of $1000.00 and is the account manager
Rep B and C are sales support
the BI sales reports needs to split the $1000.00 betwee n B & C
Rep B sales $500.00
Rep C sales $500.00
any help would be apprecieated. thanks
.
Hi FCF
In addition to amitchandak 's reply, you need to build a hierachy level table with the percentage of sales support in total.
And then you can build a new table by dax or use merge function in power query editor.
Sample table:
Percentage table:
Merge function in Power Query Editor:
Then expand Sales support and Percent column, add a custom column as below.
Result is as below.
In Dax:
You can build a calculated column in Percent table.
Sales for Sales Support = CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, Table1[Account Manager] = 'Percent'[Account Manager] ) ) * 'Percent'[Percent]Result:
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
FCF , You need to have a mapping table with One column as sales rep A and a second column as sales rep B and C. Prefer to the column for % distribution
Cross Join with filter can help. Refer these example from year to date
powerbi Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to-Daily/ba-p/1463290
refer joins: https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
Hi FCF
In addition to amitchandak 's reply, you need to build a hierachy level table with the percentage of sales support in total.
And then you can build a new table by dax or use merge function in power query editor.
Sample table:
Percentage table:
Merge function in Power Query Editor:
Then expand Sales support and Percent column, add a custom column as below.
Result is as below.
In Dax:
You can build a calculated column in Percent table.
Sales for Sales Support = CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, Table1[Account Manager] = 'Percent'[Account Manager] ) ) * 'Percent'[Percent]Result:
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi FCF
Could you tell me if your problem has been solved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
Dear Anonymous
I was having the same issue and found this solution very helpfull.
However in addition to this issue, I am trying to split the values based on a time inference.
How would I be able to add this additional layer of date in this case?
Best regards,
Ken
