Rankx with condition on a measure

[Morrison]

Hello, I would like to create a rank excluding all stores that have sales below a certain amount. 

I try this but dont run

RankX_filteronsales =

VAR PRODCUTRANKX = IF( [Tot Venduto Gara] > 264,

                                    RANKX( filter (ALL('dwh DimStore'), 'dwh DimStore'[StoreStatus] = "A" && [Tot Venduto Gara] > 264  ),

                                    CALCULATE(SUMX('Venduto Totale','Venduto Totale'[Gara_Tot_venduto]),

            DESC,

            DENSE

        ),

        BLANK ()

    )

RETURN

PRODCUTRANKX

This is the result I would like to get

Store	Sales	Rank
A	200
B	300	3
C	301	2
D	302	1

[Jihwan_Kim · Accepted solution]

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Sales measure: = 
SUM( Sales[Sales] )

Rank measure expected: =
IF (
    [Sales measure:] > 264
        && HASONEVALUE ( Store[Store] ),
    RANKX (
        FILTER ( ALL ( Store[Store] ), [Sales measure:] > 264 ),
        [Sales measure:],
        ,
        DESC
    )
)

[Morrison]

Hi @Jihwan_Kim ,

thx for the support.

I tried adding a new dimension and the function no longer returns the desired result. I'll show you what I see after adding the new AreaManager dimension. 

AreaManager	Store	Rank	Tot Sales
A	AA	1	1
B	BB	1	2
C	CC	1	3
D	DD	1	4
E	EE	1	5
F	FF	1	6
F	GG	2	7

[Jihwan_Kim]

Hi,

Thank you for your feedback.

The measure depends on how the datamodel is structured.

Please share the link of your sample pbix file with how your desired outcome looks like, and then I can try to look into it to come up with a more accurate solution.

Thanks.

[Morrison]

Hi @Jihwan_Kim ,

i solved this way. Thx

Rank =

VAR tmpVal = [Tot Q.tà gara]

RETURN

        IF(tmpVal < 60, BLANK(),

            RANKX(

                FILTER(

                        ALL('dwh DimStore'),

                        'dwh DimStore'[StoreStatus] = "A"

                        ),

               CALCULATE(SUMX('Venduto Totale','Venduto Totale'[Gara_Tot_quantita])

                ),,DESC,Dense

            )

        )