Dax weighted avg over three tables

[MichelBrown]

Hello Community,

Can anyone help me with (for me another) DAX weighted average problem?

I have three tables, two unrelated fact tables with prices and volumes and one dimension date table:

Now I want to calculate revenue but the result is incorrect on aggregated date level, so I probably first need a weighted average per time dimension (from the 20_dim_Dates table) before I can multipy with volumes

I know I should do something like this:

divide (SUMX(10_dim_Dates;PriceValue*ProductionVolume);ProductionVolume;0)

But this doesn't work. It keeps adding up the prices per day if you summerize per month or year, instead of taking a weighted average. I'm having trouble doing this over the three different tables. 

In the end my visual (or pivot table in Excel) should show price, volume and revenue per day, month or year from the 20_dim_DateTabel with a weighted average price per day, month or year and a correct sum of the revenue.

One complication: Table 110_fact_Prices contains an old and new price, labeld in the field 110_fact_Prices[PriceCategory] so my first step would probably be something like var OldPrice = calculate(sum(110_fact_Prices[PriceValue]);110_fact_Prices[PriceCategory]="Old Price") and then use the variable OldPrice in the measure. 

Can anyone help?

Thanks in advance

Michel

[MichelBrown]

Thanks for looking into the topic, here are some more details to explain:

Results per month are ok, I can easily calculate the totals in Excel with sum and sumproduct 

When I aggregate on year level, results are wrong: prices are added up and revenue is sum of volume X sum of prices, but this should be sum of volume x weighted average of prices:

This is my price measure:

And this is revenue:

And here is an attempt at weighted average price that also doesn't work:

Thanks again in advance for any support.

Michel

[v-yinliw-msft]

Hi @MichelBrown ,

Could you please explain about the details of "keeps adding up the prices per day if you summerize per month or year", and what it looks like? 

Could you please provide some sample data to remove privacy or is it a screenshot?

Best Regards,

Community Support Team _Yinliw

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