Calculate Rank of a measure by a column which is not in scope

[Anonymous]

Hello All,

I have seen lots of blogs and videos where can create ranking of a measure with a column or multiple columns from the same table or from different tables.

But all those blogs and videos showed that, those fields should be present in the visual then only the ranking will be shown properly.

But is it possible to create rank of a measure based on a column which is present in table but not in visual.

I have two fields, one is customer and another one is customer group.

Now the customer group is created to group customers into Tier1 and Tier2.

Now in visual i am using Begin date, Customer and the total sales for each customer for all the weeks.

The ranking that i want to have for Total Sales measure as

Begin of Week	Customer	Total Sales	Expected Ranking	Rule
03-01-2022	C1	8615	5	Part of Tier1 customegroup
10-01-2022	C1	8615	5	Part of Tier1 customegroup
17-01-2022	C1	8615	5	Part of Tier1 customegroup
24-01-2022	C1	8615	5	Part of Tier1 customegroup
31-01-2022	C1	8615	5	Part of Tier1 customegroup
03-01-2022	C2	9918	4	Part of Tier1 customegroup
10-01-2022	C2	9918	4	Part of Tier1 customegroup
17-01-2022	C2	9918	4	Part of Tier1 customegroup
24-01-2022	C2	9918	4	Part of Tier1 customegroup
31-01-2022	C2	9918	4	Part of Tier1 customegroup
03-01-2022	C5	10362	3	Part of Tier1 customegroup
10-01-2022	C5	10362	3	Part of Tier1 customegroup
17-01-2022	C5	10362	3	Part of Tier1 customegroup
24-01-2022	C5	10362	3	Part of Tier1 customegroup
31-01-2022	C5	10362	3	Part of Tier1 customegroup
03-01-2022	C9	10781	5	Part of Tier2 customegroup
10-01-2022	C9	10781	5	Part of Tier2 customegroup
17-01-2022	C9	10781	5	Part of Tier2 customegroup
24-01-2022	C9	10781	5	Part of Tier2 customegroup
31-01-2022	C9	10781	5	Part of Tier2 customegroup
03-01-2022	C4	10812	2	Part of Tier1 customegroup
10-01-2022	C4	10812	2	Part of Tier1 customegroup
17-01-2022	C4	10812	2	Part of Tier1 customegroup
24-01-2022	C4	10812	2	Part of Tier1 customegroup
31-01-2022	C4	10812	2	Part of Tier1 customegroup
03-01-2022	C3	11144	1	Part of Tier1 customegroup
10-01-2022	C3	11144	1	Part of Tier1 customegroup
17-01-2022	C3	11144	1	Part of Tier1 customegroup
24-01-2022	C3	11144	1	Part of Tier1 customegroup
31-01-2022	C3	11144	1	Part of Tier1 customegroup
03-01-2022	C7	11541	4	Part of Tier2 customegroup
10-01-2022	C7	11541	4	Part of Tier2 customegroup
17-01-2022	C7	11541	4	Part of Tier2 customegroup
24-01-2022	C7	11541	4	Part of Tier2 customegroup
31-01-2022	C7	11541	4	Part of Tier2 customegroup
03-01-2022	C10	11609	3	Part of Tier2 customegroup
10-01-2022	C10	11609	3	Part of Tier2 customegroup
17-01-2022	C10	11609	3	Part of Tier2 customegroup
24-01-2022	C10	11609	3	Part of Tier2 customegroup
31-01-2022	C10	11609	3	Part of Tier2 customegroup
03-01-2022	C6	11975	2	Part of Tier2 customegroup
10-01-2022	C6	11975	2	Part of Tier2 customegroup
17-01-2022	C6	11975	2	Part of Tier2 customegroup
24-01-2022	C6	11975	2	Part of Tier2 customegroup
31-01-2022	C6	11975	2	Part of Tier2 customegroup
03-01-2022	C8	12756	1	Part of Tier2 customegroup
10-01-2022	C8	12756	1	Part of Tier2 customegroup
17-01-2022	C8	12756	1	Part of Tier2 customegroup
24-01-2022	C8	12756	1	Part of Tier2 customegroup
31-01-2022	C8	12756	1	Part of Tier2 customegroup

I have created a measure which giving me right ranking for Tier2 added in visual but not when i remove it from the visual.

Top 5 = 

    RANKX(
        ALL('Table'[Customer]),
        CALCULATE(
            'Table'[Total Sales],
            ALLEXCEPT('Table','Table'[Customer],'Table'[Customer Group])
            )
            ,,
            DESC
    )
		

Any help.

Mohan V.

[amitchandak]

@Anonymous , This what I have done with my data -

measure 1 -

Customer Net = CALCULATE([net], FILTER(ALLSELECTED(sales), Sales[Customer Id] = max(Sales[Customer Id])))

you can use allexcept(sales, customer[Name]) or allexcept(customer, customer[Name])

Then I created a rank

Customer Rank = calculate(RANKX(SUMMARIZE(allselected(sales), Customer[Name], 'Item'[Category]), [Customer Net],,DESC,Dense))

again you can use all if need all(sales)

What I got

[Anonymous]

@amitchandak I request you to please have a look at the data.

The measures you gave are still not working.

Customer Net = CALCULATE('Table'[Total Sales], FILTER(ALLSELECTED('Table'), 'Table'[Customer] = max('Table'[Customer])))

Top 5 = 
calculate(RANKX(SUMMARIZE(allselected('Table'), 'Table'[Customer],'Table'[Customer Group] ), [Customer Net],,DESC,Dense))
		

Output:

My Question is can we create rank of a measure even if that column is not present in that visual.

Output that i am expecting is

Here I do not wish to have Customergroup column in my visual but still it sould give the ranking by considering customergroup and customers