Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
And then I have my measures:
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
Hi,
Here is example of the logic you can use:
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/
Proud to be a Super User!
Thanks,
Your calculation goes beyond what I was trying to calculate but thank you. Greatly appreciated.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
44 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
73 | |
50 | |
45 | |
20 | |
17 |