Index a measure in DESC order

[o59393]

Hi all

I need to add a measure that returns me in descendent order an index updated to any slicer I choose.

This is the table that for sensitive matters I cut the data:

In the example above I am selecting one product which is produced in 6 different locations (that's why it appeats 6 times above)

The correct result should be from 1 to 6 because they all have different demand.

The measure I have is the following:

Index = 

RANKX (
    FILTER (
        ALLSELECTED ( 'SKU by line - Official' ),
        'SKU by line - Official'[Country of Manufacturing]
                = MAX ( 'SKU by line - Official'[Country of Manufacturing] )
            && 'SKU by line - Official'[Manufacturing plant of production]
                = MAX ( 'SKU by line - Official'[Manufacturing plant of production] )
            && 'SKU by line - Official'[Manufacturing line]
                = MAX ( 'SKU by line - Official'[Manufacturing line] )
            && 'SKU by line - Official'[Package material]
                = MAX ( 'SKU by line - Official'[Package material] )
            && 'SKU by line - Official'[Refilability]
                = MAX ( 'SKU by line - Official'[Refilability] )
            && 'SKU by line - Official'[Volume] 
                = MAX ( 'SKU by line - Official'[Volume] )
            && 'SKU by line - Official'[Category]
                = MAX ( 'SKU by line - Official'[Category] )
            && 'SKU by line - Official'[Product name]
                = MAX ( 'SKU by line - Official'[Product name] )
            && 'SKU by line - Official'[Date] 
                = MAX ( 'SKU by line - Official'[Date] )
    ),
    CALCULATE ( [Demand by bottler (Year +1)] ),
    ,
    DESC,
    DENSE
)

How can I get it right?

Please click on the image to see right resolution.

Thanks.

[parry2k · Accepted solution]

@o59393 try new INDEX function as shown below:

Index = 
 RANK (
     DENSE,
     ALLSELECTED ( 
        'SKU by line - Official'[Country of Manufacturing], 
        'SKU by line - Official'[Manufacturing plant of production],
        'SKU by line - Official'[Manufacturing line],
        'SKU by line - Official'[Line Efficiency (real) Year 1],
        'SKU by line - Official'[Product name]
      ),
     ORDERBY ( [Demand by bottler (Year +1)], DESC )
 )

Output

[o59393]

You rock @parry2k 

Thank you so much.

[parry2k]

@o59393 sounds good, it will be much easier. Cheers!

[o59393]

Hi @parry2k 

See for example in the image below that when I use a slicer, the index is now showing 1,2,3,4,etc by each row (which is what I'd like to have).

Also note that the total is 1 but it should be 4 because it showed 4 rows/results.

Thanks for your help once again.

[parry2k]

@o59393 it is hard to work thru these screen shots, you need to put together some sample data in a pbix file and share, mainly following what your data model looks like.   

[o59393]

Sure @parry2k , let me prepare a sample. It has some sensitive data.

Thanks.

[parry2k]

@o59393 not sure how your model look like, also why you are using MAX:

Index = 
RANKX (
   ALLSELECTED ( 'SKU by line - Official' ),

    CALCULATE ( [Demand by bottler (Year +1)] ),
    ,
    DESC,
    DENSE
)

[o59393]

Hi @parry2k 

The Index changes but still doesn't do the incremental by 1 in each row:

See the demand column which has different values and the index (first column) stills repeats values.

Thanks.