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

View solution in original post

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

View solution in original post

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

View solution in original post

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

View solution in original post

DebbieE Senior Member
Senior Member

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

perfect thank you

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 56 members 1,221 guests
Please welcome our newest community members: