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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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 )

 

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.