topic Re: How to get measure to show top ranking category? in DAX Commands and Tips

How to get measure to show top ranking category?

Anonymous — Fri, 26 Nov 2021 02:31:11 GMT

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?

Re: How to get measure to show top ranking category?

VahidDM — Fri, 26 Nov 2021 02:53:19 GMT

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/

Re: How to get measure to show top ranking category?

Anonymous — Fri, 26 Nov 2021 03:22:00 GMT

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.

Re: How to get measure to show top ranking category?

VahidDM — Fri, 26 Nov 2021 06:18:22 GMT

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/