Calculate Sales contribution % on specific items

[hidenseek9]

Hi I have a question on how to calculate sales % of specfic items. 

I have a sample sales data as below. 

MTD Sales with Brand/SKU Name/Sales

Sample Data 

What I would like to do is 

1. Set up a measure to calculate Sales% of all items except for NIKE, ADIDAS and Costco SKUs. (Refer to column F of "MTD Sales" tab)

2. Set up a measure to calculate Fee1 (Refer to column G of "MTD Sales" tab)

3. Set up a measure to calculate Fee2 (Refer to column H of "MTD Sales" tab)

Step 3 is a bit tricky because first I need to set up a DAX to calculate 5% of sales for only Costco SKUs. Then deduct those amount from total Fee2 and then apply Sales% to the remaining balance for rest of the SKUs. 

I would like the end result to look like a table in "End Result" tab. 

Appreciate your support!

H

[Anonymous]

Hi @hidenseek9 ,

I created a sample pbix file(see the attachment), please check if that is what you want.Fee 2 =

Best Regards

[hidenseek9]

@Anonymous 

Thank you!

This works perfectly. 

However, is there a sum to show the right figures?

For instance,

• % needs to add up to 100% and not 131.91%.
• Fee 1 total should show as 1% of sum of sales and not 79M

How can we achieve this?

[Anonymous]

Hi @hidenseek9 ,

I updated the sample pbix file(see the attachment), please check if it is correct now...

Best Regards

[hidenseek9]

@Anonymous 

Thank you! Now the total is showing up to be 100% but the subtotal is incorrect now. 

AND1% should add up to 26.7% and subtotal amount should add up to 16.0M. 

Same issue persist for Fee2. 

Can this be solved?

[Anonymous · Accepted solution]

Hi @hidenseek9 ,

I updated the sample pbix file(see the attachment) again, please check if it is what you expected.

Best Regards