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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
alexa_0028
Resolver II
Resolver II

Calculate rank over dimensions from different tables

Hi PBI Experts,

I have a scenario where I have customer and product dimensions connected to my facts table "Sales" in a star schema.
The 2 dimension tables are not related.
I want to rank my table like below by descending measure and applying rank on each row of the table. 

Main goal : Rank measure based on customer and product combination.
My outcome:
Rank = RANKX(SUMMARIZE('Sales','Customer'[Customer],'Product'[Product]),[Sales],,DESC,Dense)

alexa_0028_1-1630581776726.png

 


Expectation:

alexa_0028_0-1630581641818.png

Can someone pls help me here?

I will highly appreciate your help.

 


 

4 REPLIES 4
alexa_0028
Resolver II
Resolver II

Hi @floriangaerner /All,

Meanwhile, I applied some slicers so that I don't get the error for threshold increase.
I have a doubt in the output. My ranking doesn't seem to get update after applying slicer. I sorted the measure DESC in visual but my ranking is not starting from 1,2,3,4 and so on. Its like 0,52 and weird numbers.

Can you pls help me to fix it?

alexa_0028_0-1630600921748.png


Appreciate your time & help here pls.

Hi  @alexa_0028 ,

 

Could you pls show us your .pbix file?

Remember to remove the confidential information.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

alexa_0028
Resolver II
Resolver II

@floriangaerner 

thank you so much for your help !!
Indeed, it looks like very promising, and something that should work and suits my requirement. 

But when I am running this in my model I am getting error out , maybe due to my large data model and the mode is Direct Query.

alexa_0028_0-1630588356578.png



So, I tried running it on import mode directly. But still it is so slow to return any result and getting error out too.

alexa_0028_1-1630588753133.png

 


Is there any other quicker way to solve this issue? As in excel it is very quick to sort and apply rank, I am not sure why in PBI it is so different.




floriangaerner
Frequent Visitor

Hello @alexa_0028 ,

may I propose you a solution:

 

I've created .pbix file to show the outcome. Based on your description I have following data model:

floriangaerner_0-1630586253468.png

 

The tables are filled according to your description.

I assumed that you want to group sales for multiple Customer-Product combinations together.

 

My proposed measure:

Proposed Rank =

RANKX (
    ALLSELECTED ( Sales ),
    CALCULATE (
        SUM ( Sales[Sale] ),
        ALLEXCEPT ( Sales, 'Customer'[Customer_Key], 'Product'[Product_Key] )
    ),
    ,
    DESC,
    DENSE
)

 

Result:

floriangaerner_1-1630586809691.png

 

Please find attached the .pbix file.

Hope I could help!

Calculate rank over dimensions from different tables.pbix 

Helpful resources

Announcements
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! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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