Rank function

[jokelaa]

Hi!

If I have a table showing dimension and a measure (lets say Store and sales)

I want to add ranking for each store based on sales. 

This is easy.

But what is the formula for set analysis if I want to add a KPI showing this ranking number for a selected store based on the whole data set. 

Everything I have tried returns 1 since the data is showing sales for this single store.

[tresesco · Accepted solution]

You have to disregard the selection (use set analysis) to bring all the stores so that the rank can be calculated against all stores. If you are trying this in a text/kpi object, try like:

Aggr(Rank(Sum({1}Sales)), Store)      // or {<Store>} instead of '{1}'

If in a chart, you might not need aggr.

[jokelaa]

Thank you!!

I was on the right track and had tried the other one you suggested. For some reason that one does not work but the other one did. Can you explain why is this so 🙂

Below pics for both versions. The one above works but the one below does not.

What am I missing?

Location is our version of store

[tresesco · Accepted solution]

The second option mightn't not work if you have other selections as well.