Forum Discussion

SebSchoon1's avatar
SebSchoon1
Icon for Post Patron rankPost Patron
3 years ago

RANK on RANK IF a condition is met

Hi Fellows!

 

I have a problem with Rank based ont conditions

 

i have created kind of index on Item Sizes. which i consider as a Rank (Step in Power query)

 

Then i made in my table 'Base Article'  ==> 

 

Rank TAILLE = RELATED('Base Article (2)'[Rank Taille])

To have it in my 'Base Article'

 

Here is my model

 

 

 

 I have simply calculated Rank like this

 

Rank Taille = CALCULATE(SUMX('Base Article (2)',[Rank Taille]),

                FILTER('Tableau Dispatch','Tableau Dispatch'[CODE DEPOT] <> BLANK()))

 

Which give me the expected information. ( it provide me the number of sizes linked to each item [CODE ARTICLE]

 

Now i'd like to RANK that information based on Items which have accros all warehouses, only one quantity

 

I have this result by doing this.

 

Nbre de tailles = CALCULATE(if([Total quantités tous dépots]=1,
                CALCULATE(COUNT('Base Article'[TAILLE]),
                     'Tableau Dispatch',
                            FILTER(ALL('Tableau Dispatch'),[Stock]<>0))))

 

Then 

Nbre de tailles 2 = Var _table=SUMMARIZE('Base Article','Base Article'[CODE ARTICLE],'Base Article'[TAILLE],

                       "_value",
                               [Nbre de tailles])

                        RETURN
                            sumx(_table,[_value])

and finaly

 

Nbr Taille TOTSTOCK 1 = if([Nbre de tailles 2]<>BLANK(),
                              CALCULATE(sumx('Base Article',[Nbre de tailles 2]),
                                       ALLSELECTED('Base Article')),BLANK())

 

This give me the expected results,  But now, impossible to get a ranked value Accros Initial Rank ([Rank Taille])

 

and the condition Where [Nbr Taille TOTSTOCK 1] is not blank

Here is a picture of my table

 

 

 

i beg you for your help super members !!!

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi SebSchoon1 ,

     

    I think you can try to create a virtual table in your rank measure and then get rank in it.  Due to I don't know your data model , here I will give you some suggestions.

    Firstly create a virtual table by summairze which should have all columns you need in your rank calculation, sometimes it shuld look like your table visual. You can check it by create use that code to create a calcualted table in data model and compare the table with your visual to check whether it is correct.

    Then add a rank column with your conditions in your virtual table. Finally, get rank from that virtual table.

    Rank = 
Var _SUMMARIZE = SUMMAIRZE('Base Article',...)
VAR _ADDCOLUMN = ADDCOLUMN(_SUMMARIZE,"RANK",RANKX(...))
Return
SUMX(_ADDCOLUMN,[Rank])

     

    Best Regards,
    Rico Zhou

     

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

    • SebSchoon1's avatar
      SebSchoon1
      Icon for Post Patron rankPost Patron
      3 years ago

      Hello,

       

      I showed a picture of my model here above

       

      here is the link of my sample data file ^^

       

      https://we.tl/t-vTIH5233Tq

       

       

      i'll test your solution and come back to you asap !!