topic Re: weighted average calculation with a twist in DAX Commands and Tips

weighted average calculation with a twist

wenchi — Tue, 08 Sep 2020 17:16:32 GMT

We have a fact table stores weekly data at store and brand with quantity and unit price. The users need to obtain the volume weighted average price by taking the lates week unit price weighted by the entire selected period quantity at store\brand level. For example, if the user choose week 50 to week 60 time span, the weighted average price is calculated with total volume of that time frame apply with week 60th store\brand price. Since Dax is not supported to create dynamic calculate table ( I thought I can create a dynamic table to store latest price for calculation, but i was wrong), I am kind of stuck to get it calculate correctly. Below are some sample data with calculation based on different weeks selection. Any helps are appreciated. Thanks.

Re: weighted average calculation with a twist

dedelman_clng — Tue, 08 Sep 2020 20:44:26 GMT

Hi wenchi -

I'm able to get your desired results for the 1 and 2 week tables showing store and brand, but I'm uncertain as to how the average per brand is calculated. Try this for the Latest Price and Extended Price, and let me know how VWA should be calculated.

Latest Price = var __lDate = LASTNONBLANK(WAvg[WE Date], MIN(WAvg[Price])) return CALCULATE(MIN(WAvg[Price]), __lDate) Ext = CALCULATE(SUM(WAvg[Qty]) * [Latest Price])

Hope this helps

David

Re: weighted average calculation with a twist

dedelman_clng — Tue, 08 Sep 2020 20:43:17 GMT

I poked around a little more and think I figured out VWA

VWA = var __LNB = LASTNONBLANK(WAvg[WE Date], MIN(WAvg[Qty])) RETURN CALCULATE(SUMX(WAvg, [Ext]) / SUM(WAvg[Qty]), __LNB)

Hope this helps

David

Re: weighted average calculation with a twist

amitchandak — Wed, 09 Sep 2020 16:08:19 GMT

wenchi , if not resolved, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: weighted average calculation with a twist

wenchi — Tue, 15 Sep 2020 19:47:59 GMT

I figure it out how to get it to work. with summarized followed by addcomoums, i get the correct number.

Re: weighted average calculation with a twist

Anonymous — Wed, 14 Oct 2020 06:48:38 GMT

Hi Wenchi,

Could you share your solution please? and i suppose you mean AddColumns?

Thanks