Weightings based upon Sales

[ElliotP]

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

[Greg_Deckler]

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?

[ElliotP]

1/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.

[v-huizhn-msft]

Hi @ElliotP,

Your only post one sample table. Based on my understanding, you want to get Weighted Proportions calculate the in table 'itemdetailsdogfood$', and will calculate the NewPrice using 'itemslist'[price] which is located 'itemslist' table, right? There is a related relationship between the tables?

If it does and have a relationship, you can create a calculated column using the formula to calculate the weighted proportion.

Weighted Proportions  = 'itemdetailsdogfood$'[Net Sales]/CALCULATE(SUM( 'itemdetailsdogfood$'[Net Sales]),ALLEXCEPT('itemdetailsdogfood$','itemdetailsdogfood$'[item]))

Then you can calculate the NewPrice using RELATED function, like the formula: New price=Related('itemslist'[price]) * Weighted Proportions.

Best Regards,
Angelia

[ElliotP]

That's a good idea. The first part works but I'm unable to complete the measure for some reason;

Newprice = RELATED('itemdetailsdogfood$'[Weighted Proportions]) * Price

The tables have a relationship yet it doesn't recognise 'itemdetailsdogfood$'[Weighted Proportions]) for some reason or the Price column in my itemlist table (which the measure is being created in).

Ideas?

[ElliotP]

Any idea how to Sum the prices of the items sold? The Prices of the products are in a different table (all in the originally linked pbix) to the sales, but they have a relationship and are linked.

So if 15 Smoothies are sold at the price point of $7, that's $105.

If 10 Scrambled Eggs are sold at the price point of $15, that's $150.

The total sum of the prices sold is $255, divide this number by the sum of the number of the products sold (countrows) and arrive at $9.

If we did the first method I suggested we'd arrive at number around $5.58. The $9 calculated figure is the one I actually want (sorry).

So, how do we calculate the sum of the total prices of items sold. Something like COUNTROWs of the items multiplied by their individual price point?

[Greg_Deckler · Accepted solution]

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.