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
Anonymous
Not applicable

Ranking in matrix doesn't work with filter

Hello everyone ! 


I'm trying to rank my total weight per client (name 1 in the matrix).

To do this, I used this formula which gives me a correct ranking :

 

Ranking  =
VAR Customers =
ALLSELECTED(ZV048[Name 1])
VAR Result =
CALCULATE(
RANKX(Customers, ZV048[Total weight formula],, DESC, DENSE ),
REMOVEFILTERS( ZV048 ),
VALUES('Calendar'[Date].[Année]),
VALUES(ZV048[Name 1]),
ALLSELECTED(ZV048[Shipping type])
)

RETURN
Result

 

Ilias69_4-1647526941952.png

 

 

However, when I use my Shipping type filter, the ranking does not act intelligently : 

 

Ilias69_3-1647526820301.png

 

Indeed, by choosing a shipping type filter I would like to have a ranking starting from 1.

 

Any idea? 

 

Thank you.

 

Regards,

 

Ilias

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @Anonymous ,

 

Please check this formula.

Ranking =
CALCULATE (
    RANKX (
        FILTER (
            ALLSELECTED ( ZV048 ),
            ZV048[Name 1] = SELECTEDVALUE ( ZV048[Name 1] )
        ),
        ZV048[Total weight formula],
        ,
        DESC,
        DENSE
    ),
    ALLSELECTED ( 'Calendar'[Date].[Année] )
)

If it doesn't work, please share the pbix to us so that we could test formula.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

3 REPLIES 3
v-jayw-msft
Community Support
Community Support

Hi @Anonymous ,

 

Please check this formula.

Ranking =
CALCULATE (
    RANKX (
        FILTER (
            ALLSELECTED ( ZV048 ),
            ZV048[Name 1] = SELECTEDVALUE ( ZV048[Name 1] )
        ),
        ZV048[Total weight formula],
        ,
        DESC,
        DENSE
    ),
    ALLSELECTED ( 'Calendar'[Date].[Année] )
)

If it doesn't work, please share the pbix to us so that we could test formula.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
amitchandak
Super User
Super User

@Anonymous , Try

RANKX(Summarize(allselected(ZV048), ZV048[Name 1],'Calendar'[Date],ZV048[Shipping type] ), ZV048[Total weight formula],, DESC, DENSE )

 

 

Anonymous
Not applicable

Thank you @amitchandak ,

 

Here is the result using this formula :

 

Ilias69_0-1647605391458.png

 

Various clients have the same ranking.

 

And using a shipping type filter: 

 

Ilias69_1-1647605391471.png

 

 

Maybe something wrong with my Total weight formula ?

 

Total weight formula = SUM(ZV048[Total Weight])
 
Ilias

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.