AllExcept and filter

[AlwaysBI]

HI

I need some help.

I have a table below.

Year	Month	Day	Product	Type	Group	Price
2019	3	1	A	Own	First Group A	100
2019	3	1	B	Competitor	First Group A	120
2019	3	1	C	Own	First Group B	90
2019	3	1	D	Competitor	First Group B	80
2019	3	5	E	Own	First Group C	150
2019	3	5	F	Competitor	First Group C	170
2019	3	7	G	Own	First Group D	150
2019	3	7	H	Competitor	First Group D	120

and I would like to show the competitor price in every group itself.

Below is the measure I use and the expected result that I want to archive. Please advise.

CompetitorPricebyGroup =

CALCULATE(AVERAGE('Table'[price]),FILTER(ALLEXCEPT('Table','Table'[Day],'Table'[Month],'Table'[Group]),'Table'[Type]= "Competitor"),ALL('Table'))

Expected Result

Year	Month	Day	Product	Type	Group	Price	Competitor Price
2019	3	1	A	Own	First Group A	100	120
2019	3	1	B	Competitor	First Group A	120	120
2019	3	1	C	Own	First Group B	90	80
2019	3	1	D	Competitor	First Group B	80	80
2019	3	5	E	Own	First Group C	150	170
2019	3	5	F	Competitor	First Group C	170	170
2019	3	7	G	Own	First Group D	150	120
2019	3	7	H	Competitor	First Group D	120	120

[AlB · Accepted solution]

Hi @AlwaysBI 

You can create a calculated colum in the table you show (Table1):

Competitor Price =
CALCULATE (
    DISTINCT ( Table1[Price] );
    ALLEXCEPT ( Table1; Table1[Group] );
    Table1[Type] = "Competitor"
)