cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Calculate Average per category

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

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?

1 ACCEPTED SOLUTION
Solution Sage

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.

11 REPLIES 11
Community Champion

Try this MEASURE.

Earlier typically works in a Column not a MEASURE

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

Regards
Zubair

Helper II

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

Community Champion

@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

Regards
Zubair

New Member

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

Community Champion

@mschultens

Try your own formula as a calculated column.

Regards
Zubair

New Member

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.

Solution Sage

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.

Helper IV

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

Frequent Visitor

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

Helper IV

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)``````

New Member

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