Problem with Wieghted Average

[remychag]

Dear all,

I am facing a problem regarding the weighted average formula.

Here is what I need to do: calculate an average of the L/C column weighted by the volume:

Group 1	L/C	Volume TO
Areva	2,69%	1111
Areva	3,35%	1205
Areva	3,36%	965
Areva	3,29%	888
Areva	0,00%	33
Areva	0,00%	21
Areva	0,00%	40

I use the formula "Weighted average per category" in "New Quick Mesure" and I put L/C in Base Value, Volumes in Weight and Group1 in Category but it doesn't work. The final average is not weighted. I have 1,81% as a result but, I should have 3,09%.

Do you know where does it come from?

Is there any other way to do it? (Be aware that the Excel used behind PBI is full of clients and dates, not only Areva).

Thanks for your help!!!

Rémy

[amaleranda]

Hi, 

Not sure what you want.

I have created weighted average based on the data you have provided. 

used below three measures

---------Measure 1-----------

TotalVolumeTO =

CALCULATE(

SUM('Table'[Volume TO]),

ALL('Table')

)

---------Measure 2-----------

Weight =

DIVIDE(

CALCULATE(

SUM('Table'[Volume TO])),

[TotalVolumeTO]

)

 

---------Measure 3-----------

WeightedAverage =

CALCULATE(SUM('Table'[L/C]) * [Weight])

 

[v-yuta-msft · Accepted solution]

@remychag ,

You can create a measure using dax below:

Average = CALCULATE(AVERAGE('Table'[L/C]), FILTER('Table', 'Table'[L/C] <> 0))

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.