Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Weighted Average

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

Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    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_
    )

     

3 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    5 years ago

    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_
    )

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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!