Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

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

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.

Mohan1029_0-1658384567273.png

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

Mohan1029_1-1658384663594.png

 

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

Begin of WeekCustomerTotal SalesExpected RankingRule
03-01-2022C186155Part of Tier1 customegroup
10-01-2022C186155Part of Tier1 customegroup
17-01-2022C186155Part of Tier1 customegroup
24-01-2022C186155Part of Tier1 customegroup
31-01-2022C186155Part of Tier1 customegroup
03-01-2022C299184Part of Tier1 customegroup
10-01-2022C299184Part of Tier1 customegroup
17-01-2022C299184Part of Tier1 customegroup
24-01-2022C299184Part of Tier1 customegroup
31-01-2022C299184Part of Tier1 customegroup
03-01-2022C5103623Part of Tier1 customegroup
10-01-2022C5103623Part of Tier1 customegroup
17-01-2022C5103623Part of Tier1 customegroup
24-01-2022C5103623Part of Tier1 customegroup
31-01-2022C5103623Part of Tier1 customegroup
03-01-2022C9107815Part of Tier2 customegroup
10-01-2022C9107815Part of Tier2 customegroup
17-01-2022C9107815Part of Tier2 customegroup
24-01-2022C9107815Part of Tier2 customegroup
31-01-2022C9107815Part of Tier2 customegroup
03-01-2022C4108122Part of Tier1 customegroup
10-01-2022C4108122Part of Tier1 customegroup
17-01-2022C4108122Part of Tier1 customegroup
24-01-2022C4108122Part of Tier1 customegroup
31-01-2022C4108122Part of Tier1 customegroup
03-01-2022C3111441Part of Tier1 customegroup
10-01-2022C3111441Part of Tier1 customegroup
17-01-2022C3111441Part of Tier1 customegroup
24-01-2022C3111441Part of Tier1 customegroup
31-01-2022C3111441Part of Tier1 customegroup
03-01-2022C7115414Part of Tier2 customegroup
10-01-2022C7115414Part of Tier2 customegroup
17-01-2022C7115414Part of Tier2 customegroup
24-01-2022C7115414Part of Tier2 customegroup
31-01-2022C7115414Part of Tier2 customegroup
03-01-2022C10116093Part of Tier2 customegroup
10-01-2022C10116093Part of Tier2 customegroup
17-01-2022C10116093Part of Tier2 customegroup
24-01-2022C10116093Part of Tier2 customegroup
31-01-2022C10116093Part of Tier2 customegroup
03-01-2022C6119752Part of Tier2 customegroup
10-01-2022C6119752Part of Tier2 customegroup
17-01-2022C6119752Part of Tier2 customegroup
24-01-2022C6119752Part of Tier2 customegroup
31-01-2022C6119752Part of Tier2 customegroup
03-01-2022C8127561Part of Tier2 customegroup
10-01-2022C8127561Part of Tier2 customegroup
17-01-2022C8127561Part of Tier2 customegroup
24-01-2022C8127561Part of Tier2 customegroup
31-01-2022C8127561Part 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.

 

2 REPLIES 2
amitchandak
Super User
Super User

@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

amitchandak_0-1658387013265.png

 

Anonymous
Not applicable

@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:

Mohan1029_0-1658388579364.pngMohan1029_1-1658388617053.png

 

 

 

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

Mohan1029_3-1658390293001.png

 

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.