AllExcept and filter

Question

HI&nbsp;I need some help.I have a table below.YearMonthDayProductTypeGroupPrice201931AOwnFirst Group A100201931BCompetitorFirst Group A120201931COwnFirst Group B90201931DCompetitorFirst Group B80201935EOwnFirst Group C150201935FCompetitorFirst Group C170201937GOwnFirst Group D150201937HCompetitorFirst Group D120&nbsp;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.&nbsp;CompetitorPricebyGroup = CALCULATE(AVERAGE('Table'[price]),FILTER(ALLEXCEPT('Table','Table'[Day],'Table'[Month],'Table'[Group]),'Table'[Type]= "Competitor"),ALL('Table'))&nbsp;Expected ResultYearMonthDayProductTypeGroupPriceCompetitor Price201931AOwnFirst Group A100120201931BCompetitorFirst Group A120120201931COwnFirst Group B9080201931DCompetitorFirst Group B8080201935EOwnFirst Group C150170201935FCompetitorFirst Group C170170201937GOwnFirst Group D150120201937HCompetitorFirst Group D120120&nbsp;

alb · Accepted Answer

Hi AlwaysBI&nbsp;
You can create a calculated colum in the table you show (Table1):
&nbsp;
Competitor Price =
CALCULATE (
    DISTINCT ( Table1[Price] );
    ALLEXCEPT ( Table1; Table1[Group] );
    Table1[Type] = "Competitor"
)

Forum Discussion

AllExcept and filter

1 Reply

Related content

Filtering with ALLEXCEPT()

Filter and Allexcept

Filter with ALLEXCEPT

ALLEXCEPT for multiple columns/Filters

FILTER ALLEXCEPT

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