How to get measure to show top ranking category?

[Anonymous]

Hello everyone,

I am having a bit of trouble with to referencing the top ranking Sales Category. I have created a measure which produces the ranking of categories based off sales:

RankSales =
RANKX( ALLSELECTED('Sales'[Category]), CALCULATE( SUM('Sales'[Sales Amount])),  , DESC, Dense)

If I put Category and RankSales in a table, it produces the following:

I want to create a measure which will tell us what is the top ranking Category (SALOON AND CAB), and another measure which shows the Sales Amount for that Category. 

Any tips?

[VahidDM · Accepted solution]

HI @Anonymous 

Try these measures

Category Top =
VAR _A =
    FILTER (
        ADDCOLUMNS (
            'Sales',
            "Rank",
                RANKX (
                    ALLSELECTED ( 'Sales'[Category] ),
                    CALCULATE ( SUM ( 'Sales'[Sales Amount] ) ),
                    ,
                    DESC,
                    DENSE
                )
        ),
        [Rank] = 1
    )
RETURN
    MAXX ( _A, [Category] )

and 

Sales Amount Top =
VAR _A =
    FILTER (
        ADDCOLUMNS (
            'Sales',
            "Rank",
                RANKX (
                    ALLSELECTED ( 'Sales'[Category] ),
                    CALCULATE ( SUM ( 'Sales'[Sales Amount] ) ),
                    ,
                    DESC,
                    DENSE
                )
        ),
        [Rank] = 1
    )
RETURN
    SUMX( _A, [Sales Amount] )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

Hello VahidDM, 

Thank you for solving this.

Quick question, I am trying to filter the ranking for a certain period (say within the last 7 days). To do this, I am replacing your line of: 

CALCULATE ( SUM ( 'Sales'[Sales Amount] ))

with

CALCULATE ( SUM ( 'Sales'[Sales Amount] ), DATESINPERIOD('Sales'[DATE], TODAY(), -7, DAY)

The previously mentioned [RankSales] still shows 'Saloon and Cab' as the top rank but the measure doesn't seem to update the category as 'Saloon and Cab'.

Any thoughts? 

Thanks again for your time and efforts. 

Any thoughts? Thank you for your time and efforts. 

[VahidDM · Accepted solution]

Hi @Anonymous 

You are welcome.

Change that ALLSELECTED ( 'Sales'[Category] ) to 'Sales' and check it then.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[VahidDM · Accepted solution]

HI @Anonymous 

Try these measures

Category Top =
VAR _A =
    FILTER (
        ADDCOLUMNS (
            'Sales',
            "Rank",
                RANKX (
                    ALLSELECTED ( 'Sales'[Category] ),
                    CALCULATE ( SUM ( 'Sales'[Sales Amount] ) ),
                    ,
                    DESC,
                    DENSE
                )
        ),
        [Rank] = 1
    )
RETURN
    MAXX ( _A, [Category] )

and 

Sales Amount Top =
VAR _A =
    FILTER (
        ADDCOLUMNS (
            'Sales',
            "Rank",
                RANKX (
                    ALLSELECTED ( 'Sales'[Category] ),
                    CALCULATE ( SUM ( 'Sales'[Sales Amount] ) ),
                    ,
                    DESC,
                    DENSE
                )
        ),
        [Rank] = 1
    )
RETURN
    SUMX( _A, [Sales Amount] )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/