topic Re: Top Rank Industries based on Revenues and Filter to only Current Year in DAX Commands and Tips

Top Rank Industries based on Revenues and Filter to only Current Year

reendzz98 — Mon, 09 Jan 2023 01:16:40 GMT

Hi everyone,

I am having a bit of trouble to sum up top 3 categories with highest revenue only for the most current year.

So far, I have the measure for the ranking part, and I have a measure that can filter out the data only to the current year but the problem is I can't merge these 2 measures to get me the output I am expected.

Ranking measure:

RANKX( ALLSELECTED(company[industry]), CALCULATE( SUM(table1[revenue1]) + SUM(table1[revenue2])) , , DESC, Dense)

Filtering to current year:

FILTER( ALL(table1), table1[rev_year].[Year] = MAX(table1[rev_year].[Year]) && table1[rev_year].[Date] <= MAX(table1[rev_year].[Date])

I want to create a measure which can help me to get top 3 industries with highest revenue but only based on the current year. Any suggestion?

Re: Top Rank Industries based on Revenues and Filter to only Current Year

Adam_Shelton — Mon, 09 Jan 2023 01:44:54 GMT

I think you need only one DAX, using condition in calculate function. Try something like this:

var a = max(table1[rev_year].[year]) return RANKX( ALLSELECTED(company[industry]), CALCULATE(SUM(table1[revenue1]) + SUM(table1[revenue2]),table1[rev_year].year=a), , DESC, Dense)

Re: Top Rank Industries based on Revenues and Filter to only Current Year

reendzz98 — Mon, 09 Jan 2023 02:01:50 GMT

Hi Adam_Shelton ,

Thank you for your respond. I tried the suggested measure but the output I received is as below:

Re: Top Rank Industries based on Revenues and Filter to only Current Year

Adam_Shelton — Mon, 09 Jan 2023 02:49:11 GMT

Try fix this:

I assume you have a date table, edit:

var a = max(date.[year]) return RANKX( ALLSELECTED(company[industry]), CALCULATE(SUM(table1[revenue1]) + SUM(table1[revenue2]),table1[rev_year]=a)

I tried by using this sample data and it workd

Re: Top Rank Industries based on Revenues and Filter to only Current Year

reendzz98 — Mon, 09 Jan 2023 02:56:15 GMT

Hi Adam_Shelton ,

I tried the new measure and it worked well!

Thanks a lot for your help!