Top 10 with a measure

[tex628]

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:

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

[v-yuta-msft]

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 · Accepted solution]

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]

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

[v-yuta-msft]

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

[Anonymous]

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]))

[rohitMe]

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