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

[reendzz98]

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? 

[Adam_Shelton]

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)

[reendzz98]

Hi @Adam_Shelton ,

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

[Adam_Shelton · Accepted solution]

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

[reendzz98]

Hi @Adam_Shelton ,

I tried the new measure and it worked well! 

Thanks a lot for your help!