DAX weighted average based on another dimension

[mhph]

Hello,

I have the following data:

DATE        COUNTRY     ITEM        Value
2005-01-01  UK          op_rate     30%
2005-01-01  UK          proc        1000
2005-01-01  UK          export      750 
2005-01-01  ITA         op_rate     45%
2005-01-01  ITA         proc        500 
2005-01-01  ITA         export      350

Basically, data in normal format, which includes both ratios (the op_rate) and other items such as exported volumes and processed volumes ("proc").

I need to aggregate by SUM for "proc" and "export", but not for the "op_rate", for which I need a weighted average by "proc".

In this case the aggregated op_rate would be: 0.45*500 + 0.30*1000 = 0.35 // instead of a .75 SUM or 0.375 AVERAGE

All example I find for weighted average are across measures, but none covers using other dimensions.

>> The model is simplified -- and the data structure is fixed

Any help most welcome!

[v-huizhn-msft]

Hi @mhph,

Please try to create a calculated column using the formula:

weight=CALCULATE (SUM(CALCULATE (min(table[Value]),filter(table,table[ITEM]='op_rate'))*CALCULATE (min(table[Value]),filter(table,table[ITEM]='proc')),ALLEXCEPT(table, table[COUNTRY]))

Then create a measure based on the [weight] column.

Best Regards,
Angelia

[Anonymous]

The way you store your data looks a bit weird, I feel like one column for each information would be much better:

DATE, COUNTRY, ITEM, OP_RATE, PROC, EXPORT

Specially if they are linked together !! 

Could you detail a bit more the result you want to reach ? I did get the weighted average thing:

0.45*500+0.3*100 = 0.35*1500

but not really the result you want to reach.. Is it a table, a graph a measure ?? 

[mhph]

Hello - thanks for your reply

I am looking for a measure, so that when I am filtering by op_rate it won't just sum it but do the weighted average.

I have thought of pivoting, but it would be unpractical, as (1) there are also other dimensions ("product"), and (2) sometimes I need to sum up Items, sometimes I aggregate by country, sometimes across time, so calculations are across all dimension consistently - logic needs to be coded within the measure.

[Anonymous]

Hey again @mhph,

I don't know if I understand the phrasing you use, when you say "Filtering by op_rate" for me it's just returning a table with "op_rate" values.. 

Also from what I understood you have 3 values grouped together for each product sold: ( op_rate, proc, export ) do you have a similar product ID for those to recognize which op_rate corresponds to which proc and which export value ? 

Could you provide the result you want to reach like an example? It is very easy and well formatted to do copy paste from excel and insert screenshot ! 

🙂