Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Morrison
Helper I
Helper I

Rankx with condition on a measure

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

 

StoreSales Rank
A200 
B3003
C3012
D3021
1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_1-1669035921344.png

 

 

Jihwan_Kim_0-1669035887561.png

 

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

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_1-1669035921344.png

 

 

Jihwan_Kim_0-1669035887561.png

 

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

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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. 

 

AreaManagerStoreRankTot Sales
AAA11
BBB12
CCC13
DDD14
EEE15
FFF16
FGG27

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors