Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Weighted Average

Hello, 

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

Lorenzo_98_0-1625219947885.png

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

1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
Community Support

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

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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! 

 

 

 

 

 

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
Community Support
Community Support

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

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.