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

[DebbieE]

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

[Zubair_Muhammad · Accepted solution]

@DebbieE

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

[AlB]

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 

[DebbieE]

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)

[AlB · Accepted solution]

@DebbieE

Download file with working example here

[DebbieE]

perfect thank you

[Zubair_Muhammad · Accepted solution]

@DebbieE

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

[DebbieE]

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