Measure to calculate TOP 1

[MarceDF]

Hi everyone, I have this data:

Group Age	Qty
25 - 34	1992
19 - 24	1726
45 - 54	1316
35 - 44	948
63 - 72	925
55 - 62	803
'+ 72	725
7 to 14	497
15 - 18	444
4 to 6	149
0 - 3	86

The QTY is a measure that is called [Active Members]

I created this measure that give me the Age Group with most Active Members:

Which age is the most active (is working) give me the 25 - 34 group=

CALCULATE (

    MAX ( 'dimAgeGroup'[Group Age] ),

    TOPN ( 1, ALL ( 'dimAgeGroup'[Group Age] ), [Active Members], DESC ),

    VALUES ( 'dimAgeGroup'[Group Age] )

)

   But when I want to make the opposite (does not work) give me the 72+ group:

Which age is the less active =

CALCULATE (

    MIN ( 'dimAgeGroup'[Group Age] ),

    TOPN ( 1, ALL ( 'dimAgeGroup'[Group Age] ), [Active Members], ASC ),

    VALUES ( 'dimAgeGroup'[Group Age] )

)

   What I am doing wrong?

[ValtteriN]

Hi,

Top 1 is actually returning a list like this in your dax:

Instead of using Top1 try using RANKX

Top 1 with rank =

CALCULATE(MAX(dimAgeGroup[Group Age]),

FILTER(dimAgeGroup,RANKX(ALLSELECTED(dimAgeGroup),[qty sum])=1))

RANKX also has the option for ASC/DESC and you can use it like this:

Top 1 with rank =

CALCULATE(MAX(dimAgeGroup[Group Age]),

FILTER(dimAgeGroup,RANKX(ALLSELECTED(dimAgeGroup),[qty sum],,ASC)=1))

Here is an example with ASC:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[MarceDF]

Hi,

Thank you for the explanation. I tried to translate to the report and only for the biggest qty it works, for the less, don't. I changed the MAX to MIN and the ASC to DESC and also played with the combinations but didn't succed.

Which age is the less active =

CALCULATE ( MIN ( 'dimAgeGroup'[Group] ),

    FILTER ( 'dimAgeGroup' ,

        RANKX ( ALLSELECTED ( 'dimAgeGroup' )

            , [Active Members] , , DESC

        ) = 1

    )

)

   If I use like this I receive 72+

Which age is the less active =

CALCULATE ( MIN ( 'dimAgeGroup'[קבוצה] ),

    FILTER ( 'dimAgeGroup' ,

        RANKX ( ALLSELECTED ( 'dimAgeGroup' )

            , [Active Members History] , , ASC

        ) = 1

    )

)