cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Filtering based on RANKX

Hi All,

 

I have three calculated columns created in one table name "INVENTORY":

(1) Having ITEM values which are coming from connected second "ITEM" Table using Related function

(2) Having Grade values which are coming from connected third "GRADE" Table using Related function

(3) Calculation done on "INVENTORY" table to get Summation of few columns of inventory table row wise, name: "SUMMED VALUE"

 

Output is like this using Matrix visual:

1.png

 

 

I need to only show one value of GRADE every row, i.e. minimum value For e.g. For ITEM =80026506 only 16A should come. While others having one Grade value have no impact.

 

I tried implementing this using RANKX function:

Test = CALCULATE(SUM(INVENTORY[SUMMED VALUE]))

RANK = RANKX(ALLSELECTED(INVENTORY[GRADE]),[Test],,ASC)

 

2.png

 

When I am applying filter on Rank = 1, it is showing no records. How can I implement this?

 

The reason for this I suspect is that RANKX is not working correctly on SUM(SUMMED VALUE) field. 

 

Please help!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Re: Filtering based on RANKX

Hi Maggie,

 

I could resolve this issue using below formula:

 

Test = 
CALCULATE (
    [SUMMED VALUE],
    FILTER (
        INVENTORY,
        NOT ( ISBLANK ( INVENTORY[GRADE] ) )
            && [SUMMED VALUE] > 0
    )
)

 Then using RANKX:

Rank = 
IF (
    NOT ( ISBLANK ( [Test] ) ),
    RANKX (
        FILTER ( ALL ( INVENTORY[GRADE] ), [Test] ),
        [Test],
        ,
        ASC,
        DENSE
    ),
    BLANK ()
)

The issue was happening due to duplicates and blank values. It resolved the issue

View solution in original post

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: Filtering based on RANKX

Hi @mannsandhu963

I can get the right result as you expected.

sumvalue = CALCULATE(SUM(Sheet1[sum value])) 
rank = RANKX(ALLSELECTED(Sheet1[grade]),[sumvalue],,DESC)

1.png

 

Best Regards

Maggie

Highlighted
Helper I
Helper I

Re: Filtering based on RANKX

Hi Maggie,

 

Thanks for the reply!

Using "DESC" in RANKX as u suggested below alows me to filter the visual. That is great but it doesn't solve my objective. Let me explain below:

I used these formulas as given by you:

Test = CALCULATE(SUM(INVENTORY[SUMMED VALUE]))
RANK = RANKX(ALLSELECTED(INVENTORY[GRADE]),[Test],,DESC)

 

Results are:

3.png 

As I mentioned: I need to show values of "SUMMED VALUE" column in visual as per this logic:

(1) This value should be >0 (As "SUMMED VALUE" can be negative and 0, show I appled visual filter on this column as greater than 0.)

(2) If for >0 there are two or more than two values it should show minimum from them (E.g. For GRADE = 80026506, two values are coming: 22 and 3242, It should show 22 only. After using DESC it is showing 3242)

(3) If for >0 only one value is there then that value should come as it is. It is coming from the DAX you shared.

 

Please let me know if you need PBIX file of it. 

 

Thanks for the help!

 

Highlighted
Helper I
Helper I

Re: Filtering based on RANKX

Hi Maggie,

 

I could resolve this issue using below formula:

 

Test = 
CALCULATE (
    [SUMMED VALUE],
    FILTER (
        INVENTORY,
        NOT ( ISBLANK ( INVENTORY[GRADE] ) )
            && [SUMMED VALUE] > 0
    )
)

 Then using RANKX:

Rank = 
IF (
    NOT ( ISBLANK ( [Test] ) ),
    RANKX (
        FILTER ( ALL ( INVENTORY[GRADE] ), [Test] ),
        [Test],
        ,
        ASC,
        DENSE
    ),
    BLANK ()
)

The issue was happening due to duplicates and blank values. It resolved the issue

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors