HOW TO COMPUTE AVERAGE OF VALUE FILTERED BY TWO CATEGORIES AS CALCULATED COLUMN - NOT MEASURE

[Anonymous]

Hi everyone,

I am having issues in computing the average of a value by filtering by two categories.

I have already tried a couple of solutions found in similar posts but nothing seems to work. The one that was close, but not good enough (the results were not always correct) was the following:

=CALCULATE(AVERAGE(Table[GROSS_YIELD]),ALLEXCEPT(Table,Table[CATEGORY_1],Table[CATEGORY_2]))

Also, for the scope of the work i cannot use measures because the results have to be summarized in a new column of my dabatase, that works like this:

ID	CATEGORY_1	CATEGORY_2	GROSS_YIELD	AVG_GROSS_YIELD
Product1	a	x	10%	20%
Product2	b	y	5%	7,5%
Product3	a	y	20%	15%
Product4	b	x	5%	5%
Product5	a	y	10%	15%
Product6	b	y	10%	7,5%
Product7	a	x	30%	20%

Ultimately, the point is to have a new column in my db where the average of gross yield is filtered by the two categories (so that when two or more products have the same categories i can see the same result).

Thanks to anyone who can help!

[Anonymous · Accepted solution]

To everyone who can be ineterested, I had an error in my db so the formula:

=CALCULATE(AVERAGE(Table[GROSS_YIELD]),ALLEXCEPT(Table,Table[CATEGORY_1],Table[CATEGORY_2]))

Works perfectly

[amitchandak]

@Anonymous , Try like

CALCULATE(AVERAGEX(Table,Table[CATEGORY_1] =earlier(Table[CATEGORY_1]) && Table[CATEGORY_2]=earlier(Table[CATEGORY_2])) Table[GROSS_YIELD])

Also, check if CATEGORY_1 and CATEGORY_2 is not making a unique combination

[Anonymous]

I tried what you suggested (added a comma before the value): 

CALCULATE(AVERAGEX(Table,Table[CATEGORY_1] =earlier(Table[CATEGORY_1]) && Table[CATEGORY_2]=earlier(Table[CATEGORY_2])), Table[GROSS_YIELD])

but the ouput is an error as EARLIER refers to a previous row that does not exist, i guess it has something to do with unique combinations of the two categories