Matrix Report Ranking

[glmoon5]

Hello,

I have a Matrix that has 4 levels in it.

Currently my last column is my Rank column that is broken.

At the top level there are 52 rows (will vary of course) and I want to rank them 1-52 in the last column (blue arrow) based on the result of the column to left (red arrow) , then when I expand all down one level in hierarchy I want to Rank those from 1 to whatever the number of rows are in level 2 and then repeat for level 3 and 4.

This give me the ability to understand that a row at level two that is ranked #1 doesn't not neccessarily have to come from the #1 ranked level 1.

This matrix has 3 slicers that might effect the context of what is showing

Any help?

[Syndicate_Admin]

Hello

Let me translate what you mention, you say that you have a table with 4 levels for example Region, Country, City, Store. You want the last column to be a dynamic ranking with column 1,2,3 at each level expanded separately. I imagine that you would also have slicers that when combining the context must respect the current level of expansion and not mix with other levels? You confirm to me if this is how you are asking since your order is not basic level.

Thank you

[glmoon5]

Yes that is what I would like and yes I do have some slicers

[v-hjannapu]

Hi @glmoon5,

Thank you  for reaching out to the Microsoft fabric community forum.
Thank you @Ashish_Mathur , @Syndicate_Admin , @Greg_Deckler , @ajaybabuinturi for your reply regarding the Issue.

Yes, what you're trying to do is fully possible in Power BI. We can create a DAX measure using ISINSCOPE() so that ranking changes automatically based on which level is expanded, and it will also follow whatever filters or slicers are applied. If you want, I can help you try this with some example data

I tested it with my sample data, and it worked fine. Please find the attached Pbix for your reference.
Hope the above provided information help you resolve the issue, if you have any further concerns or queries, please feel free to reach out to us.
Regards,
Harshitha.

[glmoon5]

@v-hjannapu Thank you. This works perfectly for my Level 1. However when going down to level 2 I see that my #1 Ranked Level 1 has 5 rows at level 2 ranked #1 and the 6th and last row as ranked 54. Similar inconsistent results happen as I continue looking at other level 2 rows. I don't believe I have the ability to add my pbix file to this post. How can I do that.

[v-hjannapu · Accepted solution]

Hi @glmoon5,
To Attach the Pbix.file just go to the reply option and click on it you will see the browse option as below screenshot. 
Here you can attach the file.

or I'm providing the link you can refer:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

You can upload your data into google drive or drop box and you can paste the link here.
Best Regards,
Harshitha.

[v-hjannapu]

Hi @glmoon5 ,
Just checking in to see if you had a chance to follow up on our earlier conversation. If you're still encountering the issue, please share the sample data so we can assist you with an accurate solution.

If you have any further questions, feel free to reach out anytime.

Regards,
Harshitha.

[v-hjannapu]

Hi @glmoon5,

Hope everything’s working fine now! If you’ve found a fix, feel free to share it and mark the answer that helped you as a solution. It really helps others in the community.

If we don’t hear from you soon, we’ll close this thread for now. But if you ever need help again, just start a new thread, we’re always here to support you.

Regards,
Harshitha.

[glmoon5]

@v-hjannapu Thank you for the .pbix. I will take a look at it and try to convert to my data. Did you see the previous link I had to my actual test data?

Matrix Rank 

[ajaybabuinturi]

Hi @glmoon5,
As mentioned @Greg_Deckler, it is easy to understand the requirement if you provide the sample .pbix file with all your measures.

Thanks,

[Greg_Deckler]

@glmoon5 What is the formula for your rank measure? What does your underlying data look like? Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[glmoon5]

@Greg_Deckler thank you for the response. I have reviewed the previous topic and not found a solution. I can certainly provide the data as text but a little unsure exactly which set of text? Export data from the Matrix visual? Go to table view and copy the raw table? When you mention use the table tool in the editing bar I am not sure where that is. Any guidance is greatly appreciated.

[Greg_Deckler]

@glmoon5 Read the second link that I posted, it demonstrates what data is needed and how to post a question properly so that all the information is available to answer it.

[glmoon5]

@Greg_Deckler Thanks for the update:

Here is my table data. What I want to accomplish it tȯ put a Rank on the very last column called Increm Sales Rating. That column is the calculation of IF(Incremental Sales per Promo <0, (-Increm Sales per Promo) * Increm Sales per MD $), (Increm Sales per Promo) * Increm Sales per MD $).

With my matrix being 4 levels:

Description

Promotion

Division

List of Promo Weeks

Starting with the Top level "Description" I want the rank to be 1 - through the total number of Descriptions are displayed. Theņ at level 2 "Promotion" I want the ranking to be based on the total number of promotion rows. Then the same for level 3 "Division" and level 4 "List of Promo Weeks.

Matrix Rank Text File 

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[glmoon5]

@Ashish_Mathur @I prefer to share with you privately. Can you tell me how I can do that?

[Ashish_Mathur]

Kindly share it here itself so that others can contribute as well.