cancel
Showing results for
Did you mean:
Established Member

## DAX Get the Average and Find the Maximum for both the category and the Value

I have the following example table (JUst popped in a few examples

Date Category Value

01/01/2018    A    20

01/01/2018    A    23

01/01/2018    B    1

01/01/2018    C    1

01/01/2018    B    34

01/01/2018    D    2

etc

So I need to find the Average for each category and then just show the category with the Max Average. So this would be 2 measures. The Max average and the category with the Max Average I believe

Getting the average is easy. So for example

Avg = AVERAGE(Table1[Value])

Date               Category        Avg            Total Count of Category

01/01/2019     A                    31.33         3

01/01/2019     B                    1.00           1

01/01/2019     C                    12.50         2

01/01/2019     D                   24.00          1

So by the end of this I would want to be left with (I added a slicer on date)

Date               Category        Avg            Total Count of Category

01/01/2019     A                    31.33         3

Any help would be appreciated

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User

## Re: DAX Get the Average and Find the Maximum for both the category and the Value

@DebbieE

One way could be to use a TOPN visual filter as follows

Super User

@DebbieE

6 REPLIES 6
Highlighted
Super User

## Re: DAX Get the Average and Find the Maximum for both the category and the Value

@DebbieE

One way could be to use a TOPN visual filter as follows

Established Member

## Re: DAX Get the Average and Find the Maximum for both the category and the Value

I was worried that would want it as a DaX query but fingers crossed they may be happy with that. Thank you so much. Ill just make sure that they are happy and mark as solved

Super User

## Re: DAX Get the Average and Find the Maximum for both the category and the Value

Hi @DebbieE

Try this:

1. Create this measure that uses the one ([Avg]) that you already have:

```ShowMeasure =
IF (
[Avg]
= MAXX (
CALCULATETABLE (
DISTINCT ( Table1[Category] );
ALL ( Table1[Category] )
);
[Avg]
);
1
)```

2. Place [ShowMeasure] in the visual level filters and select 'Show items when the value is' --> 1

Established Member

## Re: DAX Get the Average and Find the Maximum for both the category and the Value

I cant get this DAX working unfortunately. Should it contain ;   ? It goes wrong when I get to ALL

I tried changing ; to , and I still get the syntax for AVG is incorrect (My AVG measure is working fine)

Super User

@DebbieE