Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |