Can't create rank based on items in stock

[SebSchoon1]

Hi guys,

it is really strange, i have a table which is related to my dimension Table, 

However impossible to get the number of sizes available in stock (where quantity is above 0)

here is my model

My stock measure 

Stock = SUM('Tableau Dispatch'[QUANTITE PHYSIQUE])

I'd like to count the number of Sizes ( [TAILLE] Which are in stock

I made this formula to calculate how many sizes are accros all the warehouses ([CODE DEPOT])

Total quantités tous dépots = VAR QTE= [2-Total articles en stock]

                              VAR QTEDEPOT = CALCULATE(if([2-Total articles en stock]<>0,[2-Total articles en stock],BLANK()),ALLSELECTED('Tableau Dispatch'[CODE DEPOT]))

                              RETURN CALCULATE(If([Rank]<>BLANK(),QTEDEPOT,BLANK()),VALUES('Tableau Dispatch'[CODE DEPOT]))

Which give me the following result

Which is totally correct.

Now i'd like to count separately each size which has previously been taken into account.

any ways to do so??

[amitchandak]

@SebSchoon1 , I think Rank measure is missing. Please remember, If I create rank  on Article and add article id in the visual , then Article  will rank inside article id , you might end up getting 1

Rankx(Allselected(Article[Article]), [Stock measure])

add all used in visual

Rankx(Allselected(Article[Article],Article[Article ID]), [Stock measure])

or create at table level

Rankx(Allselected(Article), [Stock measure])

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

or

Power BI Rank Across dimension tables: https://youtu.be/X59qp5gfQoA

[SebSchoon1]

Hello,

Sorry for that, i forgot to mention that i'd like to get the Rank based on the availability in store.

Here is my group of measures to get it.

Nbre de tailles 4 bis = CALCULATE(IF([Total quantités tous dépots]>0,
       DISTINCTCOUNT('Base Article'[TAILLE]),0))

To Get 1 for each item which is somewhere in stock (With Wrong Total ==> 13)

Nbre de tailles 5 bis = IF(COUNTROWS(VALUES('Base Article'[TAILLE]))=1,[Nbre de tailles 4 bis],

         SUMX(VALUES('Base Article'[TAILLE]),[Nbre de tailles 4 bis]))

 To get Correct total (which is 9) since only 9 sizes are in stock.

Nbre de tailles 3 = VAR SummaryTable =
    ADDCOLUMNS (
        SUMMARIZE (
            'Tableau Dispatch',
            'Base Article'[CODE ARTICLE]
        ),
        "@val",
            VAR QTETOT = [Nbre de tailles 5 bis]
            VAR QTETOTDEPOT =
                CALCULATE ([Nbre de tailles 5 bis],
                    ALL( 'Base Article'[TAILLE] )
                )
            RETURN
                    QTETOTDEPOT
    )

RETURN
 
    IF(CALCULATE(SUMX( SummaryTable,[@val]),ALL('Base Article'[CODE ARTICLE]))=0,
        blank(),
             CALCULATE(SUMX( SummaryTable,[@val]),ALL('Base Article'[CODE ARTICLE]))

Which return me this result

So Then The Rank ([RANK TAILLE]) Must go at maximum to 9 

So expected result should be

 Here is my sample

https://we.tl/t-vTIH5233Tq

😛

Thank you so much for helping me!

[SebSchoon1 · Accepted solution]

Found the Solution

Made first

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])

Then

Nbr Taille TOTSTOCK 1 = if([Nbre de tailles 2]<>BLANK(),

                        CALCULATE(sumx('Base Article',[Nbre de tailles 2]),  

                                    ALLSELECTED('Base Article')),BLANK()) 

**bleep** DAX is really hard!