Price weighted average on 25% of the volume

[Anonymous]

Hello Power BI community,

I have been working on Power BI for a few months now and I would like to carry out a measure that would allow me to calculate the weighted average on 25% of my values, let me explain myself:
I have an Excel file (https://www.dropbox.com/scl/fi/omo3a...8dqkaiyjk0jxkp) which gathers all the data for a year of production (about 150 000 lines) with 6 columns: Month Year, Product code, Production line, Unit sold, Forecasted unit sold and Price (k€).
My objective: to calculate the weighted average price over a quarter of the total sales volume. At the end, I would like this measure to return a value in €/unit sold, i.e. the weighted average price for the 25% of my products that have the lowest price/unit sold.
On the attached graph, I have tried to graphically represent what I want. It shows 1 year of production, each colour is a product and the height of the rectangle is the number of units sold.
I tried to draw in red what I wanted. Graph weighted average
The horizontal red line represents a quarter of the total volume of units sold (about 475/4 = 120) (this is different from the 1st quartile of a serie).
Hence, I want to calculate the weighted average price of products that have 0 to approximately 120 units sold (graphically reading).
Weighted average = Sum of the price (k€)/Sum of the number of units*1000 -> Results in €/ unit

I hope that someone will understand my explanation and can help me on this point... as I am struggling with it for a while now. That would be really great.

Thanks to all of you!

[v-robertq-msft]

Hi, @Anonymous 

According to your requirement, I restore the blank value of [Unit sold] in the Power query, and you said you want to calculate the weighted average starting from the first value that is positive and growing until reaching 25% of the total. I think I can understand your logic. I changed my steps, you can take a look:

1. I created a calculated column:

Rank1 = RANKX(ALL('Sheet1'),[Unit sold],,ASC,Skip)

2. I created two measures:

rollingtotal = 
CALCULATE(SUM(Sheet1[Unit sold]),FILTER(ALLSELECTED(Sheet1),[Rank1]<=MAX([Rank1])))

weighted average1 =

var _totalsold=SUMX(ALLSELECTED(Sheet1),[Unit sold])

var _sumofprice=CALCULATE(SUM(Sheet1[Price (€)]),FILTER(ALLSELECTED(Sheet1),[rollingtotal]<=DIVIDE(_totalsold,4)))

var _sumofunits=CALCULATE(SUM(Sheet1[Unit sold]),FILTER(ALLSELECTED(Sheet1),[rollingtotal]<=DIVIDE(_totalsold,4)))

return DIVIDE(_sumofprice,_sumofunits)*1000

Unfortunately, I can’t get the weighted average dynamically based on the Slicer, because Slicer can not affect the value of the calculated column but I should use the calculated column to get the rank number to calculate the rolling total.

Would you like to check if this method is useful because my computer is too slow to run such a pbix file with a large quantity of data?

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-robertq-msft I tried to make it clearer and attached the excel file on which I normally calculate the weighted average, here is how I do it:

1. (cell F1) The column "Price (€/t)" is sorted from smallest to largest.
2. (cell H1) Sum(Sales (t))/4 this will give us the value corresponding to ¼ of the volumes, this will be our cut-off value up to which we will calculate the weighted average.
3. (cell G38) Ex : on this file we have Sum(Sales (t))/4 = 18916 so we will calculate the weighted average of the Price from line 2 to line 38 (Cumulative SUM of Sales (t) from line 2 to line 38 = 18027)
4. (cell H1) Calculation of the weighted average of the Price with = SUM (Price (k€) /SUM Sales (t) *1000

I want to be able to calculate the weighted average for different months and also I want to be able to filter with the production line (knowing the weighted average of the 25% only for P9 for example)

I hope I made it clearer!

[v-robertq-msft]

Hi, @Anonymous 

According to your description, I created a measure and some charts to achieve your requirement based on my understanding, you can take a look:

1. I delete some row data with blank values which I think you don’t need and created a measure:

weighted average =

var _rank=RANKX(ALLSELECTED(Sheet1),CALCULATE(MAX([Unit sold])),,ASC,Dense)

var _total=COUNTX(ALLSELECTED(Sheet1),[Product code])

var _sumofprice=CALCULATE(SUM(Sheet1[Price (€)]),FILTER(ALLSELECTED(Sheet1),_rank<=DIVIDE(_total,4)))

var _sumofunits=CALCULATE(SUM(Sheet1[Unit sold]),FILTER(ALLSELECTED(Sheet1),_rank<=DIVIDE(_total,4)))

return DIVIDE(_sumofprice,_sumofunits)*1000

2. Then I created a table chart, a Slicer, and a card chart and place it like this:

And I guess this is what you want.

You can download my test pbix file here

If this result is not what you want, you can explain your logic more clearly(use some sample data with fewer rows and the correct value of output you wanted)

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-robertq-msft wow, that was so quick! 

Actually if you delete blank values in the column "Unit sold", you will also remove non blank values from the column "Forecasted unit sold" or am I wrong? and we need those values.

Indeed when calculating the weighted average we can see that only for non blank values in "Unit sold" there is a non blank value in "Price (€)" but I still need the rows from "Forecasted unit sold" in other measures in my file.

Also how did you manage to "start" the first 25% count from the first Price value that is positive?

Maybe I forgot to explain this but in my file as you saw there are Price values that are negative but I don't want to count them in the weighted average, only starting from the first value that is positive and growing until reaching the 25% of the total.

Also, it seems that the file I downloaded is PBT_V0150 and I cannot find the sheet you showed in your screenshot.. I would like to see if the measure works for the amount of data that I have.

Thanks

[v-robertq-msft]

Hi, @Anonymous 

According to your description, I can roughly understand what you want to get: You want to calculate the weighted average price of the products which is within the lowest 25% of the total. But I find I can’t open and download your Excel file. Would you like to check and post the link again?

Thanks very much!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-robertq-msft ,

Thanks for your reply. This is exactly what I'm trying to get. Indeed I checked the link and it seems there is a problem, here is another one, hope this will work : https://www.dropbox.com/scl/fi/omo3at6ri461dwryxnp2e/2019_Sales.xlsx?dl=0&rlkey=fowtujto34a8dqkaiyjk... 

Thanks a lot!

[amitchandak]

@Anonymous , At a very intial look, I think percentile has role in this. Yet to check your file. See if some these blogs can help you

https://blog.enterprisedna.co/implementing-80-20-logic-in-your-power-bi-analysis/
https://forum.enterprisedna.co/t/testing-the-pareto-principle-80-20-rule-in-power-bi-w-dax/459
https://finance-bi.com/power-bi-pareto-analysis/
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-sum-of-the-top-80/td-p/763156

[Anonymous]

Hi @amitchandak ,

Thanks for the reply. 

Yes, I can understand that at first it looks like the percentile has a role in it but it's not exactly the same value as what I'm trying to get is the weighted average price of the products within the lowest 25% of the total -> to have this 25% I simply divide my total by 4. Whereas when you calculate the 25th percentile (first quartile), it's just that 25% of the datas are under this value, hence it's not the same value BUT if you know a way of calulating the weighted average within the first quartile, I would be very interested!

Thanks!