Fix Rank Measure

[MichaelaMul]

Hi everyone,

I made the measure below and it was working fine, but then I added the Segment to the table, so it is ranking it by segment rather than the entire product list that is filtered in the table. How do I adjust the measure to have it rank all the products?

Measure 1:
Total Dollar per $MM = SUM(TEST[Dollar per $MM per item])

Measure 2:
$ per $MM Rank = 
IF (
    NOT ( ISBLANK ( 'TEST'[Total Dollar per $MM] ) ),
    RANKX (
        FILTER ( ALLSELECTED( 'TEST'[Product] ), NOT ( ISBLANK ( [Total Dollar per $MM] ) ) ),
        [Total Dollar per $MM]
    )
)

Below is the table I made filtered by Geography, Time, Segment. Highlighted is what I want the Ranking to be

[Anonymous · Accepted solution]

@ThxAlot  @Ashish_Mathur ,thanks for your concern about this case.

Hi @MichaelaMul  , I used the data you provided and wrote the following DAX:

Rank = 
    IF(
    NOT ( ISBLANK ( 'TEST'[Total Dollar per $MM] ) ),
    RANKX (
        FILTER (
            ALLSELECTED('TEST'),
            NOT ( ISBLANK ( [Total Dollar per $MM] ) )
        ),
        [Total Dollar per $MM],
        ,
        DESC,
        Dense
    )
)

This is the result you want:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ThxAlot]

=
RANKX(
    CALCULATETABLE( VALUES( SALES[Product] ), ALLSELECTED( SALES[Product] ) ),
    [Total Dollar per $MM]
)

[MichaelaMul]

here's the sample data:

Geography	Time	Product	Segment	Price Per Units	Dollar Sales	Dollar per $MM per item
North	L4 Weeks	Almond Aspetic Brand A	Almond Aseptic	2.20	$11,502,947	1218
North	L4 Weeks	Almond Aspetic Brand B	Almond Aseptic	3.00	$861,823	1161
South	L4 Weeks	Almond Aspetic Brand A	Almond Aseptic	2.23	$571,384	1147
South	L13 Weeks	Almond Aspetic Brand B	Almond Aseptic	2.29	$6,023,622	1132
North	L4 Weeks	Almond Brand A	Almond	2.26	$1,223,131	1125
South	L4 Weeks	Oat Brand C	Oat Milk	2.26	$12,796,039	1123
North	L4 Weeks	Almond Aspetic Brand C	Almond Aseptic	2.97	$113,341	1109
South	L4 Weeks	Almond Aspetic Brand B	Almond Aseptic	3.00	$766,244	1004
South	L4 Weeks	Almond Aspetic Brand C	Almond Aseptic	2.97	$6,036,529	987
South	L13 Weeks	Oat Brand B	Oat Milk	3.02	$509,749	977
South	L4 Weeks	Almond Aspetic Brand D	Almond Aseptic	2.97	$77,351	951
North	L4 Weeks	Almond Brand B	Almond	2.23	$1,359,322	934
North	L4 Weeks	Almond Brand C	Almond	2.23	$1,236,040	883
North	L13 Weeks	Almond Brand D	Almond	2.32	$104,812	874
North	L4 Weeks	Almond Brand D	Almond	2.16	$196,572	853
South	L4 Weeks	Almond Brand A	Almond	2.32	$637,544	849
North	L4 Weeks	Oat Brand A	Oat Milk	2.26	$1,223,131	836
North	L4 Weeks	Oat Brand B	Oat Milk	2.23	$1,359,322	769
South	L13 Weeks	Oat Brand C	Oat Milk	2.32	$703,315	747
North	L4 Weeks	Oat Brand C	Oat Milk	2.23	$956,009	702
North	L13 Weeks	Oat Brand A	Oat Milk	2.22	$766,244	698
South	L13 Weeks	Almond Brand D	Almond	3.02	$454,576	698
North	L4 Weeks	Almond Aspetic Brand D	Almond Aseptic	2.97	$719,377	685
North	L13 Weeks	Almond Brand A	Almond	2.96	$835,451	663
North	L13 Weeks	Almond Brand B	Almond	2.97	$694,490	660
North	L13 Weeks	Almond Aspetic Brand A	Almond Aseptic	3.00	$106,072	658
South	L13 Weeks	Almond Brand C	Almond	2.32	$637,544	656
North	L13 Weeks	Almond Brand C	Almond	2.32	$9,155,226	650
South	L4 Weeks	Almond Brand B	Almond	2.32	$694,490	628
South	L4 Weeks	Almond Brand C	Almond	2.97	$104,812	627
South	L13 Weeks	Oat Brand A	Oat Milk	3.02	$9,157,542	581
South	L4 Weeks	Almond Brand D	Almond	2.38	$703,315	574
South	L4 Weeks	Oat Brand A	Oat Milk	2.32	$12,930,530	566
North	L13 Weeks	Oat Brand B	Oat Milk	2.26	$15,526,543	565
North	L13 Weeks	Almond Aspetic Brand B	Almond Aseptic	2.97	$719,377	558
South	L4 Weeks	Oat Brand B	Oat Milk	2.22	$626,815	552
North	L13 Weeks	Almond Aspetic Brand C	Almond Aseptic	2.97	$11,504,960	547
North	L13 Weeks	Almond Aspetic Brand D	Almond Aseptic	3.02	$835,451	479
South	L13 Weeks	Almond Aspetic Brand D	Almond Aseptic	3.02	$484,678	477
South	L13 Weeks	Almond Brand B	Almond	2.32	$553,950	454
South	L13 Weeks	Almond Aspetic Brand A	Almond Aseptic	2.96	$15,528,982	422
South	L13 Weeks	Almond Aspetic Brand C	Almond Aseptic	2.26	$2,379,752	388
South	L13 Weeks	Almond Brand A	Almond	3.02	$106,072	357
North	L13 Weeks	Oat Brand C	Oat Milk	2.23	$113,341	236

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.