Weighted Average

[Anonymous]

Hello, 

I need to compute a weighted average for the consumer price. 

Currently, CP YTD is computed as Average(DB(consumer price). However, if I use this formula I receive an arithmetic average. 

I need to weigh the consumer price for NES YTD (Sales). 

the formula needs to be something like: (CP YTD*NES YTD)/sum(NES YTD). however, I need to do that for each level.

for example:
for liv6 [(NES Ytd 541... * CP YTD 541... )/ sum (NES YTD 541... + NES YTD 541... and so on) ]

the denominator needs to be dynamically adjusted depending on the level that is analysed. 

How can I do that in DAX? 

Thanks for the help

[Anonymous]

Thanks for your reply! 

I have just some further questions?

1. What do you mean with xxx?
   In Var total_ do I need to insert the name of the table? 
2. in Var _Table is sales a new or existing table? 
   
   
3. What do Row1 and Row2 refer to? 

I really appreciate your help! 

[V-lianl-msft]

It refers to the column name of row labels. If the problem still exists, Please provide some sample data, preferably a pbix after removing sensitive data.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-... 

[V-lianl-msft · Accepted solution]

Try to use the sumx function.

WA = 
VAR total_ = 
    CALCULATE(
        [NES YTD],
        ALL(xxxxx)
    )
VAR _Table = 
    ADDCOLUMNS(
        SUMMARIZE(
            Sales,
            xxx[Row1],xxx[Row2]
        ),
        "nes", [NES YTD],
        "cp", [CP YTD]
    )
RETURN
    SUMX(
        _Table ,
        [cp] * [nes]/total_
    )