Ranking by subcategory

[CarlsBerg999]

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:

rank =

VAR RankCustomersBySales =

RANKX (

'Visuals Customer',

CALCULATE (

SUM('Visuals Customer'[Revenue]),

'Task'[Task Status] <> "Stopped",

'Sub-Task'[Status] <> "Cancelled",

ALLEXCEPT ('Visuals Customer','Visuals Customer'[Customer ID])

),

,

DESC

)

RETURN

RankCustomersBySales

However, this is not producing the expected outcome. How do i write this dax?

[v-yalanwu-msft]

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.

[smpa01 · Accepted 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
)

[Jihwan_Kim]

Rank CC =
VAR currentyear = Data[Year]
VAR year_table =
FILTER ( data, Data[Year] = currentyear )
RETURN
RANKX ( year_table, Data[Revenue],, DESC )

[amitchandak]

@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

[CarlsBerg999]

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. 

[amitchandak]

@CarlsBerg999 , for column use revenue column

RANKX ( filter( 'Visuals Customer', 'Visuals Customer'[Year] = earlier('Visuals Customer'[Year]))
,'Visuals Customer'[Revenue],,
DESC
)