Filtering based on RANKX

[Anonymous]

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:

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)

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!

[v-juanli-msft]

Hi @Anonymous

I can get the right result as you expected.

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

Best Regards

Maggie

[Anonymous]

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:

 

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!

[Anonymous · Accepted solution]

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