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
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?
Solved! Go to 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
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:
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |