Forum Discussion
Post ProdigyWeightings based upon Sales
Afternoon,
I've googled and had a look around the forums but for whatever reason I'm just not getting this; I am super tired though...
I'm trying to calculate the dynamic weights of items based upon their total contribution to Net Sales and then multiple this proportion against it's price as to then be able to attain a new dynamic average price paid.
At the moment; I have;
sumofnetsales = sum('itemdetailsdogfood$'[Net Sales]) - To calculate the total sales.
sumofnetsalesitem = Calculate(Sum('itemdetailsdogfood$'[Net Sales]), FILTER('itemdetailsdogfood$','itemdetailsdogfood$'[item])) - To calculate the proportional weightings of each item. Not sure how to do this one.
Weighted Proportions = Sumofnetsalesitem / sumofnetsales
NewPrice = [Weighted Proportions]*'itemslist'[price] ????
So the part I'm stuck on is calculating the proportion for each individual item (feels like a sumx type thing) and then being able to apply the [weighted proportions] to the price as to obtain the new price (just a measure should work)?
Here is a link to my pbix: https://1drv.ms/u/s!At8Q-ZbRnAj8hkQ0BAAxiXnUdKJm
If the item prices are in another table, then you should be able to create a calculated column in that table like:
ItemTotal = CALCULATE(SUM(Table[Price],RELATEDTABLE(Table))
That should give you the sum of all of the items of that particular type sold.
7 Replies
- Greg_Deckler
Community Champion
Can you provide an example of input and desired output (obviously you have the input in the pbix file) but are you looking for something like:
Item 1 = 33%
Item 2 = 40%
Item 3 = 20%
Item 4 = 7%
% of total sales but then you want to essentially say that if Item 1's price is $3, that the new price should be $1? 33% * 3 = 1?
ElliotP1/1/2017 ItemA $100
1/1/2017 ItemB $200
1/1/2017 ItemC $300
1/1/2017 ItemB $100
1/1/2017 ItemC $100
Item A total = $100 = 12.5% of overall.
Item B total = $300 = 37.5% of overall
Item C total = $400 = 50% of overall.
I will then take the weightings of the items and multiply them by the price of the product, so that i feel would be a simply 12.5%*$3 for item A, 37.5% for Item B, etc
As I'm typing this I agree, I realise that we'll end up with the non desired outcome. I'd like to be able to calculate the average price paid weighted by net sales. So if more expensive dishes are sold then its represented in the average price.
Thoughts?
- ElliotP
Would make more sense to sum the prices of the items sold and then divide them by COUNTROWS.