Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am applying the below RANKX formula,
India | 100 | 1 | Asia |
Pakistan | 50 | 1 | Asia |
China | 90 | 1 | Asia |
Australia | 85 | 2 | Australia |
France | 70 | 3 | Europe |
Solved! Go to Solution.
Hi @Antmkjr ,
Please check the formula.
Column =
VAR rank_ =
RANKX (
'Table',
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALL ( 'Table' ),
'Table'[Continent] = EARLIER ( 'Table'[Continent] )
&& 'Table'[Country] <> "France"
)
),
,
DESC,
DENSE
)
RETURN
IF ( 'Table'[Country] = "France", BLANK (), rank_ )
Best Regards,
Jay
Hi @Antmkjr ,
Please check the formula.
Column =
VAR rank_ =
RANKX (
'Table',
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALL ( 'Table' ),
'Table'[Continent] = EARLIER ( 'Table'[Continent] )
&& 'Table'[Country] <> "France"
)
),
,
DESC,
DENSE
)
RETURN
IF ( 'Table'[Country] = "France", BLANK (), rank_ )
Best Regards,
Jay
First >>
I can remove Sales from the context itself whenever I have France, In my case, it is "d"
New Rank = IF(Sheet3[Sub Category]="d",0,Sheet3[Column])
You can exclude these 0s in the main dashboard as needed, also Ranking will not affect, it will just exclude d/France
Regards,
Ritesh
Yes exactly
Yes excatly
@Antmkjr
Please try
Rank =
RANKX (
ALL ( 'Table'[Continent] ),
CALCULATE (
SUMX (
CALCULATETABLE (
FILTER ( 'Table', NOT ( 'Table'[Country] IN { "India", "China" } ) ),
ALLEXCEPT ( 'Table', 'Table'[Continent] )
),
'Table'[Sales]
)
)
)
HI @Antmkjr
Please try
Rank =
RANKX (
FILTER (
ALL ( 'Table'[Continent] ),
CALCULATE ( VALUES ( 'Table'[Country] ) ) <> "France"
),
CALCULATE ( SUM ( 'Table'[Sales] ), ALLEXCEPT ( 'Table', 'Table'[Continent] ) )
)
It is wrongly calculating Rank of France here as 2, as I mentioned, I want to exclude France from this Ranking.
@Antmkjr
Just to properly understand your rquirement. If we exclude India and China for example, then Asia will be ranked 3? Is this what you want?
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
84 | |
39 | |
25 | |
21 |