Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I'm trying to create a rank -column as described below. The idea is to rank the largest customers by revenue for each year. So for example, Customer A has the largest sales in each year, so it is ranked as no 1 each year. Below is the goal:
Customer ID | rank | Year | Customer Name | Revenue |
10054 | 1 | 2021 | Customer A | 4 755 136,39 € |
40039 | 2 | 2021 | Customer B | 4 027 475,56 € |
40014 | 3 | 2021 | Customer C | 2 449 284,35 € |
10054 | 1 | 2022 | Customer A | 4 717 788,69 € |
10082 | 2 | 2022 | Customer D | 2 217 147,99 € |
10054 | 1 | 2023 | Customer A | 6 408 845,84 € |
My DAX is currently as follows:
However, this is not producing the expected outcome. How do i write this dax?
Solved! Go to Solution.
@CarlsBerg999 you can achieve this with two measures
_rev =
SUM ( 'Table 1'[Revenue] )
_rank =
RANKX (
FILTER ( ALLSELECTED ( 'Table 1' ), 'Table 1'[Year] = MAX ( 'Table 1'[Year] ) ),
[_rev],
,
DESC
)
Hi, @CarlsBerg999 ;
Here is dax by create a column or create a measure as follows:
rankmeasure =
VAR RankCustomersBySales =
RANKX (
FILTER(ALL('Visuals Customer'),[Year]=MAX([Year])),
CALCULATE( SUM([Revenue])),
,
DESC,Dense
)
RETURN
RankCustomersBySales
RankCol = RANKX(FILTER(ALL('Visuals Customer'),[Year]=EARLIER([Year])),[Revenue],,DESC,Dense)
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@CarlsBerg999 you can achieve this with two measures
_rev =
SUM ( 'Table 1'[Revenue] )
_rank =
RANKX (
FILTER ( ALLSELECTED ( 'Table 1' ), 'Table 1'[Year] = MAX ( 'Table 1'[Year] ) ),
[_rev],
,
DESC
)
Rank CC =
VAR currentyear = Data[Year]
VAR year_table =
FILTER ( data, Data[Year] = currentyear )
RETURN
RANKX ( year_table, Data[Revenue],, DESC )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@CarlsBerg999 , Try measure like
Revenue M = CALCULATE (
SUM('Visuals Customer'[Revenue]),
'Task'[Task Status] <> "Stopped",
'Sub-Task'[Status] <> "Cancelled",
)
rank =
VAR RankCustomersBySales =
RANKX ( filter( summarize(allselected('Visuals Customer'), 'Visuals Customer'[Customer Name], 'Visuals Customer'[Year]),
[Year] =max([year]))
,[Revenue M],,
DESC
)
RETURN
RankCustomersBySales
or
rank =
VAR RankCustomersBySales =
RANKX ( filter( summarize(allselected('Visuals Customer'), 'Visuals Customer'[Customer Name],
'Visuals Customer'[Customer ID]
,'Visuals Customer'[Year]),
[Year] =max([year]))
,[Revenue M],,
DESC
)
RETURN
RankCustomersBySales
Hi,
These solutions use ALLSELECTED which would refer more to a visual? These are not producing the wanted outcome, I need a calculated column for this.
@CarlsBerg999 , for column use revenue column
RANKX ( filter( 'Visuals Customer', 'Visuals Customer'[Year] = earlier('Visuals Customer'[Year]))
,'Visuals Customer'[Revenue],,
DESC
)
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |