weighted average calculation with a twist

[wenchi]

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.

[wenchi]

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

[thibbos]

Hi Wenchi,

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

Thanks

[amitchandak]

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

[dedelman_clng]

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

[dedelman_clng]

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