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.

Anonymous
Not applicable

## 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

1 ACCEPTED SOLUTION
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_
)``````

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!

Community Support

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.

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

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors