Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
reendzz98
Helper I
Helper I

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

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? 

 

1 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

Adam_Shelton_2-1673232525014.pngAdam_Shelton_3-1673232536249.png

 

View solution in original post

4 REPLIES 4
Adam_Shelton
Frequent Visitor

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)

 

Hi @Adam_Shelton ,

 

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

 

reendzz98_1-1673229626637.png

 

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

Adam_Shelton_2-1673232525014.pngAdam_Shelton_3-1673232536249.png

 

Hi @Adam_Shelton ,

 

I tried the new measure and it worked well! 

reendzz98_0-1673232953816.png

 

Thanks a lot for your help!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.