Taking an average by category without ignoring row context

[SushiAddict]

Hello, I'm looking to produce a calculated measure that is able to return an average for the category that the row item is in. See example below:

Color	Sale ID	Sale Price	Average by Color
Green	1	10	15
Green	2	10	15
Green	3	20	15
Green	4	20	15
Yellow	5	2	5
Yellow	6	8	5
Yellow	7	5	5
Blue	8	30	40
Blue	9	50	40

I'm essentially looking for the DAX version of the "AverageIF" function in excel. While I am able to create a summary table displaying the colors and average by colors, that is not what I am trying to achieve. The table needs to be displayed as above, with the individual sales included. Is there a way for CALCULATE to generate a filter context based on the row context of each item?

Any help is appreciated!

[wdx223_Daniel · Accepted solution]

CALCULATE(AVERAGE(Table[Sales]),ALLEXCEPT(Table,Table[Color]))

[SushiAddict]

Thank you, this does what I had requested. I have hit another issue, if you are able to answer this question as well:

I will need this data displayed on a line graph where the data from column 'color' is not displayed. In effect, the table would look like this:

ID	Price	Average by Color
1	10	15
2	10	15
3	20	15
4	20	15
5	2	5
6	8	5
7	5	5
8	30	40
9	50	40

With the above solution, the calculated measure doesn't work if the 'color' column isn't in the graphic, even though the relationship exists in the data table. Do you know of a way around this?