weighted Average on specifc lines of table

[Christoffer12]

Hi All,

I have a simple table of country, product, sales volume, cost, sales price and margin (CM).

I want to calculate a few averages and weighted averages on it. Please see in example below. I have included the Excel "fromulas" as reference of what i am doing here. Can you help me recreating this in Power BI?
thanks in advance. 

	A	B	C	D	E	F	G	H	I	J	K
1		Prod-Type	VOL	Cost	Price	CM			Counrty	weighted Average CM	Reference Formula Excel:
2	USA	Baseline	0%	1,000	1,300	23%			US	27.0%	=SUMPRODUCT(C3:C4;F3:F4)
3	USA	Product X	40%	1,500	2,100	29%			BR	24.1%	=C6*F6+C8*F8
4	USA	Product Y	60%	2,000	2,700	26%
5	Brazil West	Baseline	0%	1,000	1,200	17%
6	Brazil West	Product X	66%	1,500	2,000	25%		Counrty	Product	Margin Upside	Reference Formula Excel:
7	Brazil East	Baseline	0%	1,000	1,200	17%		US	Product X	5%	=F3-F2
8	Brazil East	Product Y	33%	2,000	2,600	23%		US	Product Y	3%	=F4-F2
9								BR	Product X	8%	=F6-F5
10								BR	Product Y	6%	=F8-F7
11
12								Counrty	weighted Average Margin Upside	Reference Formula Excel:
13								US	3.9%	=J7*C3+J8*C4
14								BR	7.6%	=J9*C6+J10*C8

[Christoffer12 · Accepted solution]

HI rbriga.
thanks for your quick reply.

to the first one:

thanks that works.

to the second one:

If i apply the formula i get these margin upsides per product which seems to be incorrect. I am not sure if i need to transform data in any way to make the formula work. Thanks for your help to solve it 🙂

to the third one:

works if the second one is corrected.

thanks again! 🙂

[Christoffer12 · Accepted solution]

HI rbriga.
thanks for your quick reply.

to the first one:

thanks that works.

to the second one:

If i apply the formula i get these margin upsides per product which seems to be incorrect. I am not sure if i need to transform data in any way to make the formula work. Thanks for your help to solve it 🙂

to the third one:

works if the second one is corrected.

thanks again! 🙂

[rbriga · Accepted solution]

Of course! "Baseline" was eliminated by the row context. 

Let's change the measure:

Margin Upside=
AVERAGE( 'TABLE'[CM] ) - CALCULATE(
    AVERAGE( 'TABLE'[CM] ),
    ALLSELECTED( 'TABLE'[Prod-Type] ),
    KEEPFILTERS( 'TABLE'[Prod-Type] ="Baseline" )
)

[rbriga]

The first is relatively simple. 

SUMX('TABLE NAME',
'TABLE NAME'[VOL]*'TABLE NAME'[CM]
)

and put it in context.

As for the second, 

you can go with

AVERAGE('TABLE NAME'[CM])-
CALCULATE(
AVERAGE('TABLE NAME'[CM]),
KEEPFILTERS('TABLE NAME'[Prod-Type] = "Baseline")
)

And the third:

SUMX('TABLE NAME',
'TABLE NAME'[VOL]*[Margin Upside]
)

where [Margin Upside] is the measure we created in step 2.