cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tex628 New Contributor
New Contributor

Top 10 with a measure

Hello,

 

I have a report where view sales volume by supplier. In my visualisation i want to only show the top 10 suppliers. 

 

The problem is that sales volume is a measure, created by adding two other measures together.

These measures originate from 4 different columns that come from two different tables. 

 

Because this is really complicated to explain I've tried to visualize like this:

 

image.png

 

Black names are columns and red names are measures.

 

I've tried working with TOPN and with RANKX but I cant rank it on one specific column as the volume is relying on the value of more than one column!

 

Does anyone have any ideas on how to solve this? 

 

/ Johannes

1 ACCEPTED SOLUTION

Accepted Solutions
tex628 New Contributor
New Contributor

Re: Top 10 with a measure

Well i managed to solve part of the issue. A normal Rankx(ALL(Table[Column]);[Measure]) managed to give proper ranking values to all measurement values. Now the issue is that when i apply external filters the ALL() method results in the ranking ignore the current filter. 

 

This results in the remaining objects having ranks something like; 1,4,6,7,9 and therefore when i put a filter to only display rank<11 i get lesss than 10 results.

 

I can sadly not share the tables due to company policy. I will try to recreate the situation with dummy data.

 

/ Johannes

View solution in original post

6 REPLIES 6
rohitMe Regular Visitor
Regular Visitor

Re: Top 10 with a measure

Hi @tex628

 

As I understand, you want to show the top 10 suppliers and the top 10 are filtered by the Total sales volume, is this rigth? If so then you can filter your data after making the visualisation itself. 

If possible could you please provide a sample data having the given columns and measures as you have in your tables.

 

Regards,

Rohit

Anonymous
Not applicable

Re: Top 10 with a measure

You should be able to reference your calculated measure in a rankx without any issues I think:

Calculate(rankx(ALL('Table'[Supplier]),[Total Sales Volume]))
Community Support Team
Community Support Team

Re: Top 10 with a measure

Hi tex628,

 

Are you trying to rank values by 2 category columns, right? If my understanding is right, you can refer to DAX formula pattern of below:

 

Rank by Categories:
Rank = RANKX(FILTER(Table, Table[Category] = EARLIER(Table[Category])), RANKX(ALL(Table), Table1[Value]), , ASC, Dense)

If not so, could you clarify more details about your requirement, if possible, can you post your tables for further analysis?

 

Regards,

Jimmy Tao

tex628 New Contributor
New Contributor

Re: Top 10 with a measure

Well i managed to solve part of the issue. A normal Rankx(ALL(Table[Column]);[Measure]) managed to give proper ranking values to all measurement values. Now the issue is that when i apply external filters the ALL() method results in the ranking ignore the current filter. 

 

This results in the remaining objects having ranks something like; 1,4,6,7,9 and therefore when i put a filter to only display rank<11 i get lesss than 10 results.

 

I can sadly not share the tables due to company policy. I will try to recreate the situation with dummy data.

 

/ Johannes

tex628 New Contributor
New Contributor

Re: Top 10 with a measure

Well i managed to solve part of the issue. A normal Rankx(ALL(Table[Column]);[Measure]) managed to give proper ranking values to all measurement values. Now the issue is that when i apply external filters the ALL() method results in the ranking ignore the current filter. 

 

This results in the remaining objects having ranks something like; 1,4,6,7,9 and therefore when i put a filter to only display rank<11 i get lesss than 10 results.

 

I can sadly not share the tables due to company policy. I will try to recreate the situation with dummy data.

 

/ Johannes

View solution in original post

Community Support Team
Community Support Team

Re: Top 10 with a measure

Hi tex628,

 

"Now the issue is that when i apply external filters the ALL() method results in the ranking ignore the current filter. "

 

<--- Could you please provide more details about your issue?

 

Regards,

Jimmy Tao

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 331 members 3,070 guests
Please welcome our newest community members: