Forum Discussion

o59393's avatar
o59393
Icon for Post Prodigy rankPost Prodigy
3 years ago

Index a measure

Hi all

 

I need to add a measure that returns me in descendent order an index. 

 

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 plants (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 created is the following:

 

 

 

Index = 

RANKX(
    ALLEXCEPT('SKU by line - Official',
    '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'[Package material],
    'SKU by line - Official'[Refilability],
    'SKU by line - Official'[MS-SS],
    'SKU by line - Official'[Volume],
    'SKU by line - Official'[Category],
    'SKU by line - Official'[Product name],
    'SKU by line - Official'[Date]),

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

 

 

How can I get it right?

 

Note: click on the image to see right resolution.

Thanks.

Need Help
Show and Tell

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  o59393 ,

     

    You can try the following dax:

    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
)

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • o59393's avatar
      o59393
      Icon for Post Prodigy rankPost Prodigy
      3 years ago

      Hi Anonymous 

       

      It shows 1 for all the rows:

       

       

       

       

      Thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hello,

    Instead of using RankX, utilize the Rank function; it will effectively address your issue. When using the Rank function, make sure to include the entire table name as the argument( All[Table Name]). This approach will provide you with the accurate rank according to your specified criteria.

    Best regards,