How to calculate RANK correctly when there is filter

[HaNguyen97]

I have data like this:

Segment	Client	Sale Amount
A	ac	100
A	ab	10
A	bd	200
B	ac	30
B	ab	40
E	nd	300
D	ac	250
D	ab	10
D	ns	30
D	nd	20
F	bd	300
F	ac	100

First, I need to calculate rank of Segment based on total Sales Amount.

Expected result is

Segment	Client	Sale Amount	Rank
A	ac	100	2
A	ab	10	2
A	bd	200	2
B	ac	30	4
B	ab	40	4
E	nd	300	3
D	ac	250	2
D	ab	10	2
D	ns	30	2
D	nd	20	2
F	bd	300	1
F	ac	100	1

When apply filter on Client, the total amount will need to changed accordingly.

For example, when I select client "ac" or "ab", expected result is

Segment	Client	Sale Amount	Rank
A	ac	100	2
A	ab	10	2
B	ac	30	4
B	ab	40	4
D	ac	250	1
D	ab	10	1
F	ac	100	3

How to achive this?

[rajendraongole1 · Accepted solution]

Hi @HaNguyen97  - Create a measure to calculate the total sales for each segment.

Eg: Total Sales by Segment =
CALCULATE(
SUM('SalesTable'[Sale Amount]),
ALLEXCEPT('SalesTable', 'SalesTable'[Segment])
)

Now create an another measure to rank the segments based on the total sales calculated.

Rank by Segment Sales =
RANKX(
ALLSELECTED('SalesTable'[Segment]),
[Total Sales by Segment],
,
DESC,
DENSE
)

Hope it works and now add these measures to your visualization in Power BI report 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!