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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Luodada
Helper I
Helper I

Rankx the measure caculated through the BOM

Hello Everyone 

 

I am new at Power BI , and I have problem here in getting the correct Rankx

appreciate if anyone could help me out .

 

 

I have three tables 

1, Statistics Table

productperiodvolume
AppleJan320
AppleFeb650
OrangeJan110
OrangeFeb890
PearJan300
PearFeb400
BananaJan550
BananaFeb300
2000Jan400
2000Feb900

2, BridgeTable

Product
apple
orange
pear
banana
2000

3,BOM table

componentproductqty
1001apple1
1002apple2
1003apple3
1001orange1
1003orange6
1001pear1
1002pear8
1003banana2
1002banana4
200020001

the Measure I used RANKX(Statistics;[Component Volume];;ASC) , but the result is not correct as below picture , Capture.PNGCapture2.PNGany clue ?? thanks 

 

 

 

 

 
2 ACCEPTED SOLUTIONS

@Luodada

 

Try this one

 

rank =
RANKX ( ALLSELECTED ( Bridgetable[product] ), [Component Volume],, ASC, DENSE )

Regards
Zubair

Please try my custom visuals

View solution in original post

@Luodada

 

If you donot want to rank the BLANKS..then

 

rank2 =
VAR myrank =
    RANKX (
        FILTER ( ALLSELECTED ( Bridgetable[product] ), [Component Volume] <> BLANK () ),
        [Component Volume],
        ,
        ASC,
        DENSE
    )
RETURN
    IF ( NOT ( ISBLANK ( [Component Volume] ) ), myrank )

Regards
Zubair

Please try my custom visuals

View solution in original post

10 REPLIES 10
Zubair_Muhammad
Community Champion
Community Champion

@Luodada

 

May be

 

=
RANKX ( ALLSELECTED ( Statistics[product] ), [Component Volume],, ASC )

Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad

 

Thanks Zubair , but it does not work 

do you know what else might fix it Capture.PNG

 

 

@Luodada

 

Could you share your file via onedrive or googledrive?


Regards
Zubair

Please try my custom visuals

@Luodada

 

Please See file attached

 

ranking.png

 

 


Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad

 

what if I would like to remove the blank and 0 value from the ranking result ?

 

 

 

@Zubair_Muhammad

Bingo !

thanks so much!! @Zubair_Muhammad

 

you are wondeful!

 

 

 

@Luodada

 

Try this one

 

rank =
RANKX ( ALLSELECTED ( Bridgetable[product] ), [Component Volume],, ASC, DENSE )

Regards
Zubair

Please try my custom visuals

@Luodada

 

If you donot want to rank the BLANKS..then

 

rank2 =
VAR myrank =
    RANKX (
        FILTER ( ALLSELECTED ( Bridgetable[product] ), [Component Volume] <> BLANK () ),
        [Component Volume],
        ,
        ASC,
        DENSE
    )
RETURN
    IF ( NOT ( ISBLANK ( [Component Volume] ) ), myrank )

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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