rank with slicer

[Anonymous]

I'm hoping that someone can help me with a problem that I have. 

I would like to be able to rank my table that is filtered using a hierarchy slicer. 

My table has 4 columns:

Indicator ID

Reporting period

Level

Indicator value

The slicer uses Indicator ID and Reporting period

What I want to achieve is to show the ranking of the indicator value.

The table is a combined table from several appended queries.

I am able to get the ranking before the query is appended by creating 2 measures.

The first measure called sum values = sum(Table[indicator value])

The 2nd measure called rank = RANKX(ALL(Table[Level]),[sum values],,DESC)

I don't know how to go about replicating this when the queries are combined and using the slicer. 

When I try the sum values measure it just gives me the indicator value but not the sum total of the indicator value.

I have attached this picture which should help you to see what I am trying to do. 

Any help would be much appreciated. 

Tom

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

>>When I try the sum values measure it just gives me the indicator value but not the sum total of the indicator value.

Actually, I am still confused about your requirement. When you select 1_01 and 2014, the total sum of the indicator value is one value, how do you rank them? 

If you want to rank value dynamically based on the slicer, please refer to this knowledge base. When you use the hierarchy slicer, it still works.

If this still doesn't resolve your issue, please post more details and list the expected result, so that we can post solution which is close to your requirement.

Best Regards,
Angelia

[Anonymous]

Hello Angelia

Thank you for taking the time to reply to my post. Please accept my apologies for any confusion that I may have caused. 

The knowledge base article was extremely helpful.  

I can make it work if I don't combine the CSV files.

Thank you again for pointing me in the right direction. 

Regards

Tomsalford