Rankx the measure caculated through the BOM

[Luodada]

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

product	period	volume
Apple	Jan	320
Apple	Feb	650
Orange	Jan	110
Orange	Feb	890
Pear	Jan	300
Pear	Feb	400
Banana	Jan	550
Banana	Feb	300
2000	Jan	400
2000	Feb	900

2, BridgeTable

Product

apple

orange

pear

banana

2000

3,BOM table

component	product	qty
1001	apple	1
1002	apple	2
1003	apple	3
1001	orange	1
1003	orange	6
1001	pear	1
1002	pear	8
1003	banana	2
1002	banana	4
2000	2000	1

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

[Zubair_Muhammad · Accepted solution]

@Luodada

Try this one

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

[Zubair_Muhammad]

@Luodada

May be

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

[Luodada]

@Zubair_Muhammad

Thanks Zubair , but it does not work 

do you know what else might fix it 

[Zubair_Muhammad]

@Luodada

Could you share your file via onedrive or googledrive?

[Luodada]

@Zubair_Muhammad

please check if you could reach it via the link 

https://drive.google.com/file/d/1LSNQy8UgbOkHsIVjMCOJtMrkzZPkJWxu/view?usp=sharing

[Luodada]

@Zubair_Muhammad

please check this link

https://drive.google.com/drive/folders/1P8AvsM0E5Ce4N6Aw9sDsFBtWXkl_0wa9?usp=sharing

[Zubair_Muhammad]

@Luodada

Please See file attached

[Luodada]

@Zubair_Muhammad

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

[Luodada]

@Zubair_Muhammad

Bingo !

thanks so much!! @Zubair_Muhammad

you are wondeful!

[Zubair_Muhammad · Accepted solution]

@Luodada

Try this one

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

[Zubair_Muhammad · Accepted solution]

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