Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
@JeffDuzak - I saw your previous post for something similar to this...
I am working with a flat file that has gross sales totals by representative and product. I am trying to take this gross sale amount and "calculate" a split which will identify a distribution amount. For instance:
Mary has sold $50,000 of apples. 90% of the sale goes to the company ($45,000) and 10% goes to Mary ($5,000).
John has sold $20,000 of grapes. 60% of the sale goes to the company ($12,000) and 40% goes to John ($8,000).
What is the best way to calculate the "percentage-splits" by product so that the calculation in dollars for both the company and representative can be incorporated into the dashboard (for multiple products)? Additionally, the solution will account for instances where a person sells multuple products that allows for the proper split by product to be summed.
Thanks in advance.
Solved! Go to Solution.
It seems that you just need to use IF Function or SWITCH Function to add calculated columns. It would be better if you can provide some sample data and show us the expected result.
I captured a layout in Excel to better illustrate the example. (Though I don't have the skill to do the calcs.) The highlighted areas are the area in which I want Power BI to ultimately calculate or sum.
For example the transaction on 1/1/2017 would show $280 "Split to Company" and $120 "Split to Representative". These would be calculations based on the rate card on right side of this table which shows how the "Gross Sold" should be distributed.
i.e. When Bananas multiple gross sold by 70% for "Split to Company" and 30% for "Split to Representative".
Secondarily, I would be able to use the Company and/or Representative as an aggregation to sum the distribution in total to company and the individual representative.
I have also included a link to the .xlsx file:
https://www.dropbox.com/s/wlf6sonpslp6h49/Example%20Produce%20Revenue%20Split.xlsx?dl=0
If you could provide some detailed instruction and/or .pbix example that would be great as I am not very familir with IF expressions or Switch functions yet.
Much appreciated.
Hi, happy to help you.
My solution will be:
1. Related both Tables
2. Create 2 calculated columns in Transactions Table
To Company = RELATED(Distribution[To Company])*Transactions[ Gross Sold ]
ToSalesRep = RELATED(Distribution[To Representative])*Transactions[ Gross Sold ]
3. Use some Visuals like this:
or
Let me knoe if you need more help to adjust this in your scenario.
Victor
Lima - Peru
Thanks @Vvelarde
The visuals included are one aspect of what I am trying to accmplish.
What I don't see the percentage calculations specific to the product. Examples bulleted below.
Mary has sold $50,000 of apples. 90% of the sale goes to the company ($45,000) and 10% goes to Mary ($5,000).
John has sold $20,000 of grapes. 60% of the sale goes to the company ($12,000) and 40% goes to John ($8,000).
The end goal will be to see the split at the transaction level, and sum as you have illustrated in your post.
Thanks again. Always appreciative of your help.
Bingo! I think the only thing I am failing to understand is how the calculation is working in your ToCompany and ToSalesRep columns.
Can you share the .pbix file so I can see the relationship created between the tables and the complete DAX statement?
Thanks.
This is the PBIX Test File
https://drive.google.com/file/d/0B95C8CKdTZE3SVJqUEIyU2ttSzA/view?usp=sharing
Let me know if need more help
Victor
Lima - Peru
Thanks. Worked just as I had hoped.
Very much appreciate it.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |