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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Ranking and sorting

Hello experts,

Using the below function, I am getting the table1

Rank:=if(not(isblank([bias])),rankx(ALLSELECTED(IBPData[Product Group]),[Bias],,0),blank())

 

table1

Table IBPData
LocationProduct GroupVolRank
ABeer10001
AWine9502
AWater8203
BWine9801
BWater9502
BBeer9203
CBeer11001
CWater9702
CWine8103

 

However, I'd like to get the table2 (below) just showing the top 2 products with the highest vol. across all the locations:

table2

Table IBPData
LocationProduct GroupVolRank
ABeer11001
ABeer10002
BWine9803
CWater9704
DBeer9605
AWine9506
1 ACCEPTED SOLUTION

Rank := //Try this 
IF (
    NOT ( ISBLANK ( [bias] ) ),
    RANKX (
        ALLSELECTED ( IBPData[Product Group] ),
        CALCULATE ( SUM ( IBPData[Vol] ) ),
        ,
        DESC,
        Dense
    ),
    BLANK ()
)
//And then filter out this measure less than or equal to 2 in visual filter

Did I answer your question? If so, please mark my post as a solution!


Proud to be a Super User!




LinkedIn Icon
Muhammad Hasnain



View solution in original post

3 REPLIES 3
mh2587
Super User
Super User

Rank := // Try this one
IF (
    NOT ( ISBLANK ( [bias] ) ),
    VAR TopN := 2  // Specify the number of top products you want to include
    RETURN
        IF (
            RANKX (
                ALLSELECTED ( IBPData[Product Group] ),
                CALCULATE ( SUM ( IBPData[Vol] ) ),
                ,
                DESC,
                Dense
            ) <= TopN,
            RANKX (
                ALLSELECTED ( IBPData[Product Group] ),
                CALCULATE ( SUM ( IBPData[Vol] ) ),
                ,
                DESC,
                Dense
            ),
            BLANK ()
        )
)

Did I answer your question? If so, please mark my post as a solution!


Proud to be a Super User!




LinkedIn Icon
Muhammad Hasnain



Anonymous
Not applicable

Hi @mh2587 ,

I am getting this error with your sentence

GuinabaPBI_0-1714391573783.png

 

Rank := //Try this 
IF (
    NOT ( ISBLANK ( [bias] ) ),
    RANKX (
        ALLSELECTED ( IBPData[Product Group] ),
        CALCULATE ( SUM ( IBPData[Vol] ) ),
        ,
        DESC,
        Dense
    ),
    BLANK ()
)
//And then filter out this measure less than or equal to 2 in visual filter

Did I answer your question? If so, please mark my post as a solution!


Proud to be a Super User!




LinkedIn Icon
Muhammad Hasnain



Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.