Rankx Measure with Filter

Question

Hello,I am trying to adjust a current Ranking Measure to exclude certain Geographies from the Ranking.&nbsp;This is the current measure:Vol Sales Rank Geo = 
    RANKX (
        FILTER (
            ALLSELECTED('LatestSizes'), 
            NOT ( ISBLANK ( [Vol Sales]  ) )
            ),
        [Vol Sales] ,
        ,
        DESC,
        Dense
    )+0I tried the following but for some reason it still includes the excluded. As you can see below, now the ranking starts at 3 instead of 1Vol Sales Rank Geo = 
IF(sum(Geography_Table[New Sort])&gt;12,
    RANKX (
        FILTER (
            ALLSELECTED('LatestSizes'), 
            NOT ( ISBLANK ( [Vol Sales]  ) )
            ),
        [Vol Sales] ,
        ,
        DESC,
        Dense
    )+0,0)Any Suggestions?&nbsp;Sample data with the desired ranking I want:GeographyTimeProductVolume SalesNew SortDesired RankingTotal US - Multi Outlet+Latest 4 Weeks Ending 03-16-25Almond Milk3,047,7541&nbsp;Total US - FoodLatest 4 Weeks Ending 03-16-25Almond Milk2,254,6392&nbsp;Total US - DrugLatest 4 Weeks Ending 03-16-25Almond Milk16,0043&nbsp;Total New England w/ Albany - Multi Outlet+Latest 4 Weeks Ending 03-16-25Almond Milk256,8714&nbsp;Total New England w/ Albany - FoodLatest 4 Weeks Ending 03-16-25Almond Milk218,4955&nbsp;Total New England w/out Albany - Multi Outlet+Latest 4 Weeks Ending 03-16-25Almond Milk243,0996&nbsp;Total New England w/out Albany - FoodLatest 4 Weeks Ending 03-16-25Almond Milk208,6367&nbsp;Boston, MA - Multi Outlet+Latest 4 Weeks Ending 03-16-25Almond Milk99,0628&nbsp;Boston, MA - FoodLatest 4 Weeks Ending 03-16-25Almond Milk86,2299&nbsp;Northern New England - FoodLatest 4 Weeks Ending 03-16-25Almond Milk45,02210&nbsp;Northern New England - Multi Outlet+Latest 4 Weeks Ending 03-16-25Almond Milk53,89611&nbsp;New York, NY - FoodLatest 4 Weeks Ending 03-16-25Almond Milk221,19412&nbsp;Store 1Latest 4 Weeks Ending 03-16-25Almond Milk278,315133Store 2Latest 4 Weeks Ending 03-16-25Almond Milk82,175146Store 3Latest 4 Weeks Ending 03-16-25Almond Milk25,4641510Store 4Latest 4 Weeks Ending 03-16-25Almond Milk255,172164Store 5Latest 4 Weeks Ending 03-16-25Almond Milk12,2211717Store 6Latest 4 Weeks Ending 03-16-25Almond Milk60,238187Store 7Latest 4 Weeks Ending 03-16-25Almond Milk6,9291920Store 8Latest 4 Weeks Ending 03-16-25Almond Milk10,2932118Store 9Latest 4 Weeks Ending 03-16-25Almond Milk15,4622214Store 10Latest 4 Weeks Ending 03-16-25Almond Milk6,9892319Store 11Latest 4 Weeks Ending 03-16-25Almond Milk30,435249Store 12Latest 4 Weeks Ending 03-16-25Almond Milk16,8422513Store 13Latest 4 Weeks Ending 03-16-25Almond Milk304,199261Store 14Latest 4 Weeks Ending 03-16-25Almond Milk303,098272Store 15Latest 4 Weeks Ending 03-16-25Almond Milk5,1782821Store 16Latest 4 Weeks Ending 03-16-25Almond Milk22,1982911Store 17Latest 4 Weeks Ending 03-16-25Almond Milk4,6483022Store 18Latest 4 Weeks Ending 03-16-25Almond Milk13,8073116Store 19Latest 4 Weeks Ending 03-16-25Almond Milk35,465328Store 20Latest 4 Weeks Ending 03-16-25Almond Milk14,7543315Store 21Latest 4 Weeks Ending 03-16-25Almond Milk245,274345Store 22Latest 4 Weeks Ending 03-16-25Almond Milk17,2583512&nbsp;Thank you!&nbsp;

deku · Answer

Try iterating over just geography, and I assume vol sales does return blank, not 0

Vol Sales Rank Geo =
RANKX (
FILTER (
ALLSELECTED('LatestSizes'[geography]),
NOT ( ISBLANK ( [Vol Sales] ) )
),
[Vol Sales] ,
,
DESC,
Dense
)+0

v-prasare · Answer

Hi&nbsp;MichaelaMul,
Thanks for reaching MS Fabric community support.
&nbsp;
Deku, Thanks for your promt response. additionally refer below
&nbsp;
Vol_Sales_Rank_Geo =&nbsp;VAR FilterTable =&nbsp;&nbsp;&nbsp; FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ALLSELECTED('LatestSizes'),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT ISBLANK ( [Vol Sales] ) &amp;&amp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM(Geography_Table[New Sort]) &gt; 12&nbsp;&nbsp;&nbsp; )RETURN&nbsp;&nbsp;&nbsp; IF(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM(Geography_Table[New Sort]) &gt; 12,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RANKX (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FilteredTable,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Vol Sales],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DESC,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dense&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BLANK() )
&nbsp;
&nbsp;
Thanks,
Prashanth Are

michaelamul · Answer

Hi!Thank you both for your reply! I tried both and the first one just made every geography return "1" ranking. Then I tried the second one and I am getting the same problem of it starting the ranking at 3.&nbsp;&nbsp;

v-prasare · Answer

Hi&nbsp;MichaelaMul,&nbsp;as we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?
&nbsp;
&nbsp;
&nbsp;
&nbsp;
Thanks,
Prashanth Are
MS Fabric community support
&nbsp;
If this post helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly and give&nbsp;Kudos&nbsp;if helped you resolve your query

michaelamul · Answer

Hi! No, both solutions sadly didn't work. As you can see from my reply above. The 1st solution just made all of the rankings be 1 and then the 2nd solution had the same problem I was having, having the ranking start at 3.

Forum Discussion

Rankx Measure with Filter

7 Replies

Related content

RANKX with filter

Rankx with filter

Filter by Rank measure

RankX

Dynamic measure filtering

Geography	Time	Product	Volume Sales	New Sort	Desired Ranking
Total US - Multi Outlet+	Latest 4 Weeks Ending 03-16-25	Almond Milk	3,047,754	1
Total US - Food	Latest 4 Weeks Ending 03-16-25	Almond Milk	2,254,639	2
Total US - Drug	Latest 4 Weeks Ending 03-16-25	Almond Milk	16,004	3
Total New England w/ Albany - Multi Outlet+	Latest 4 Weeks Ending 03-16-25	Almond Milk	256,871	4
Total New England w/ Albany - Food	Latest 4 Weeks Ending 03-16-25	Almond Milk	218,495	5
Total New England w/out Albany - Multi Outlet+	Latest 4 Weeks Ending 03-16-25	Almond Milk	243,099	6
Total New England w/out Albany - Food	Latest 4 Weeks Ending 03-16-25	Almond Milk	208,636	7
Boston, MA - Multi Outlet+	Latest 4 Weeks Ending 03-16-25	Almond Milk	99,062	8
Boston, MA - Food	Latest 4 Weeks Ending 03-16-25	Almond Milk	86,229	9
Northern New England - Food	Latest 4 Weeks Ending 03-16-25	Almond Milk	45,022	10
Northern New England - Multi Outlet+	Latest 4 Weeks Ending 03-16-25	Almond Milk	53,896	11
New York, NY - Food	Latest 4 Weeks Ending 03-16-25	Almond Milk	221,194	12
Store 1	Latest 4 Weeks Ending 03-16-25	Almond Milk	278,315	13	3
Store 2	Latest 4 Weeks Ending 03-16-25	Almond Milk	82,175	14	6
Store 3	Latest 4 Weeks Ending 03-16-25	Almond Milk	25,464	15	10
Store 4	Latest 4 Weeks Ending 03-16-25	Almond Milk	255,172	16	4
Store 5	Latest 4 Weeks Ending 03-16-25	Almond Milk	12,221	17	17
Store 6	Latest 4 Weeks Ending 03-16-25	Almond Milk	60,238	18	7
Store 7	Latest 4 Weeks Ending 03-16-25	Almond Milk	6,929	19	20
Store 8	Latest 4 Weeks Ending 03-16-25	Almond Milk	10,293	21	18
Store 9	Latest 4 Weeks Ending 03-16-25	Almond Milk	15,462	22	14
Store 10	Latest 4 Weeks Ending 03-16-25	Almond Milk	6,989	23	19
Store 11	Latest 4 Weeks Ending 03-16-25	Almond Milk	30,435	24	9
Store 12	Latest 4 Weeks Ending 03-16-25	Almond Milk	16,842	25	13
Store 13	Latest 4 Weeks Ending 03-16-25	Almond Milk	304,199	26	1
Store 14	Latest 4 Weeks Ending 03-16-25	Almond Milk	303,098	27	2
Store 15	Latest 4 Weeks Ending 03-16-25	Almond Milk	5,178	28	21
Store 16	Latest 4 Weeks Ending 03-16-25	Almond Milk	22,198	29	11
Store 17	Latest 4 Weeks Ending 03-16-25	Almond Milk	4,648	30	22
Store 18	Latest 4 Weeks Ending 03-16-25	Almond Milk	13,807	31	16
Store 19	Latest 4 Weeks Ending 03-16-25	Almond Milk	35,465	32	8
Store 20	Latest 4 Weeks Ending 03-16-25	Almond Milk	14,754	33	15
Store 21	Latest 4 Weeks Ending 03-16-25	Almond Milk	245,274	34	5
Store 22	Latest 4 Weeks Ending 03-16-25	Almond Milk	17,258	35	12