topic Re: Weighted Average Selling Price based on volume and volume remaining in DAX Commands and Tips

Weighted Average Selling Price based on volume and volume remaining

Anonymous — Sat, 29 Jan 2022 00:27:04 GMT

I'm trying to create the DAX to write a funtion for weighted average selling price based on the volume and the volume remaining on a contract.

I have lots of dimension filters that can be adjusted.

Company
Location
Contract
Customer
Item
Item Group
Contract Type
Sales Rep
Start Date
End Date

And then I have my measures:

Price
Volume
Balance
Balance %

I'm minimize the table to show the important items, but all of my dimensions listed above are columns in the table.

Contract	Cust	Selling Price	Weighted Avg Price (Vol)	Weighted Avg Price (Bal)	Volume	Volume Balance	Balance %
33033	7445	97.88	2.575789474	0.83484735	535	107	20%
31517	5950	63.66	10.60895622	7.566126744	3388	1491	44%
36366	4544	106.45	1.534178554	2.333499402	293	275	94%
19383	3901	85.32	13.55971077	16.26146831	3231	2391	74%
33943	3138	87.22	12.37297	19.64796493	2884	2826	98%
26441	9029	91.12	11.56367929	15.36946353	2580	2116	82%
26444	3732	108.26	6.75227152	8.319062575	1268	964	76%
29092	4970	131.39	23.61530054	24.87455161	3654	2375	65%
29090	5950	60.11	7.382915396	0	2497	0	0%
Total/Average		92.3788889	89.96577177	95.20698	20330	12545	62%

Selling Price: Average calculates correctly. = 92.3788889

Total Volume = 20330

Balance = 12545

Balance % = 62%

I don't know to calculate the DAX is for these two measures:

Weighted Average Price (Vol) = Selling Price * ( Volume / Total Volume )

Example: First Row: WAPV = 97.88 * (535 / 20330 ) = 2.575789474

Aggregated average = 89.96577177

Weighted Average Price (Bal) = Selling Price * ( Balance / Total Balance)

Example: First Row: WAPV = 97.88 * (107 / 12545 ) = 0.83484735

Aggregated average bal = 95.20698

Any thoughts? I tried sum and sumx using ALL, ALLSELECTED and ALLEXCEPT, and tried AVERAGEX and the same, but I can't figure it out. Thanks

Re: Weighted Average Selling Price based on volume and volume remaining

David-Ganor — Sat, 29 Jan 2022 09:10:02 GMT

Hi Anonymous ,

Generally, only the Toal balance and Total volume should be calculated using ALL.

You should set the current values of the calculation as variables.

Something along the lines of:

Selling Price * ( Volume / Total Volume )

Var this_row_selling_price='fact_table'[selling price]

Var this_row_volume='fact_table'[volume]

Var Total_Volume=Calculate(sum ('fact_table'[volume]),ALL('fact_table'))

Return

this_row_selling_price*(this_row_volume/Total_Volume)

Hope it helps.

David

Re: Weighted Average Selling Price based on volume and volume remaining

ValtteriN — Sat, 29 Jan 2022 09:10:39 GMT

Hi,

Here is example of the logic you can use:

weighted avg = MAX('Table (8)'[Selling Price])*MAX('Table (8)'[Volume])/CALCULATE(SUM('Table (8)'[Volume]),ALLSELECTED('table (8)'))

By using MAX you can reference to row in the filter context:

The logic for the (Bal) is the same.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Weighted Average Selling Price based on volume and volume remaining

Anonymous — Mon, 14 Feb 2022 18:32:40 GMT

Thanks,

Your calculation goes beyond what I was trying to calculate but thank you. Greatly appreciated.

Re: Weighted Average Selling Price based on volume and volume remaining

Anonymous — Mon, 14 Feb 2022 18:48:17 GMT

Thanks for the solution.

The variable wouldn't allow me to just pick the measure so I had to do

VAR this_row_selling_price = SUM( 'fact table'[selling price] ) and
VAR this_row_volume = SUM( 'fact table'[volume] )

It makes the total come out to 7.7 Million when it sould be about $311.