Calculate Average per category

[mschultens]

Hello everyone,

I spent a lot of time already looking at similar problems to the one I have, but nothing really worked for me.  Assume I have a table with a category and some values.

How can I calculate a measure or a new column that shows the average of the values for one category like in my example below?

category | value | average

----------------------------

A | 2 | 1.5

A | 1 | 1.5

B | 3 | 2

B | 2 | 2

B | 1 | 2

I already tried:
AverageMeasure= CALCULATE(AVERAGE(table[value]); FILTER(table; table[category]=EARLIER(table[category]))

but it gives me an error saying that EARLIER refers to an earlier row context that does not exist. And when I put an explicit category in the FILTER statement, the result seems to be just the original value per row.

Any ideas?

[Zubair_Muhammad]

HI @mschultens

Try this MEASURE.

Earlier typically works in a Column not a MEASURE

AverageMeasure =
CALCULATE ( AVERAGE ( table[value] ), ALLEXCEPT ( Table, table[category] ) )

[zamboni1199]

Thank you!  That just helped me with a similar issue too.

[Zubair_Muhammad]

@mschultens

You can simply use Average(table[value]) as well if you choose not to put VALUE in the Table VISUAL

Your formula would work as a calculated column

[mschultens]

Thank you, but that does not work for me, because I want to put the different averages into one visualization.

[Zubair_Muhammad]

@mschultens

Try your own formula as a calculated column.

[mschultens]

It gives me an error saying that a single value for category cannot be determined. I tried putting a MAX() in front, but that gives the same error.

[v-xjiin-msft · Accepted solution]

Hi @mschultens,

Did you use the original expression in the calculated column? It works for me. Please refer:

Or you can try measure:

AverageMeasure =
CALCULATE (
    AVERAGE ( Table3[Value] ),
    FILTER ( ALLSELECTED ( Table3 ), Table3[Category] = MAX ( Table3[Category] ) )
)

Thanks,
Xi Jin.

[jochendecraene]

Hi

I'd like to use this solution, but I also have a subcategory and year that I have to take into account.

I tried using extra filters for subcategory and year, but keep getting errors.

Can someone help me?

This is what my table looks like

[sabinozen]

I'm also intrested in this. Did you solved it?

[Anonymous]

@v-xjiin-msft 

I have a question about average too

I have this data, what I want to achieve :

> Average Total : points PR+WAR from all technician

> Average PR & WAR : point only for PR OR only WAR based on FILTER

This is my code but gave me value :

YESTERDAY

> Average Total : correct value 7,5 this DAX gave me 3,75  (WRONG)

> Average PR & WAR : correct value PR = 3 and WAR = 4.5 this DAX gave me PR = 3 and WAR = 4.5 (CORRECT)

Bassicly how to make this DAX give a correct value for Average Total and Average PR & WAR 

1. AVG_Points = AVERAGE(CL_PROD[Points])

2 AVG_Points_Filter = IF(MAX(CL_PROD[WO_Type])= "PR" || MAX(CL_PROD[WO_Type])= "WAR",
	            [AVG_Points],[AVG_Points]*2)

[mschultens]

I had a mistake somewhere else in my model. Both solutions work fine! Thank you very much guys! 🙂