cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DebbieE Member
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
Super User
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

 

topn visual filter.png

Super User
Super User

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

@DebbieE

Download file with working example here

6 REPLIES 6
Super User
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

 

topn visual filter.png

DebbieE Member
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

Highlighted
Super User
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 

DebbieE Member
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
Super User

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

@DebbieE

Download file with working example here

DebbieE Member
Member

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

perfect thank you