Suggestion on DAX - Financial Data

[irnm8dn]

@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).

• i.e. When apples distribute 90% of the gross sale to company, and 10% to sales representative.

John has sold $20,000 of grapes.  60% of the sale goes to the company ($12,000) and 40% goes to John ($8,000).

• i.e. When grapes distribute 60% of the sale to the company and 10% to the sales representative.

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.

[v-chuncz-msft]

@irnm8dn,

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.

[irnm8dn]

@v-chuncz-msft

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.

[Vvelarde]

@irnm8dn

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

[irnm8dn]

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).

• i.e. When apples distribute 90% of the gross sale to company, and 10% to sales representative.

John has sold $20,000 of grapes.  60% of the sale goes to the company ($12,000) and 40% goes to John ($8,000).

• i.e. When grapes distribute 60% of the sale to the company and 10% to the sales representative.

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.

[Vvelarde · Accepted solution]

@irnm8dn

Ok, Something like this?

Table View in Data Tab

[irnm8dn]

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.

[Vvelarde]

@irnm8dn

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

[irnm8dn]

Thanks.  Worked just as I had hoped.

Very much appreciate it.