Displaying rank affected by slicer

[David_Efr]

Hi,

I'd like to create a dashboard with a few slicers that filter the visuals.

However, I'd also like to display a text that shows me the rank of the group selected in the slicer among the general population.

Example

Suppose I have this data: 3 columns from the data source and an additional calculated measure

Company	Share price	EPS	PE Ratio (calculated: [Share price] / [EPS])
Intel	52.43	4.43	11.84
IBM	139.44	9.50	14.68
Oracle	55.41	2.79	19.86

I have a single-selection slicer that selects the company.

Suppose I have "Intel" selected in the slicer.

Then, I'd like to have text that says "Rank: 1 out of 3".

If I change selection to "Oracle", I'd like to have the text change to "Rank: 3 out of 3".

My question here is how can I neutralize the slicer's effect on the grand total?

I'd like to show the "count(distinct company)" (in SQL terms) calculated on the entire population, and a similar consideration applies on the ranking.

I suppose there's a DAX solution, but I'm not too familiar with it yet...

Thanks,

David

[v-yuta-msft]

@David_Efr ,

You can create a calculate column using DAX below:

Rank =
RANKX ( Table, [PE Ratio],, desc, DENSE )

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yuta-msft]

@David_Efr ,

Suppose I have this data: 3 columns from the data source and an additional calculated measure

Company	Share price	EPS	PE Ratio (calculated: [Share price] / [EPS])
Intel	52.43	4.43	11.84
IBM	139.44	9.50	14.68
Oracle	55.41	2.79	19.86

I have a single-selection slicer that selects the company.

Suppose I have "Intel" selected in the slicer.

Then, I'd like to have text that says "Rank: 1 out of 3".

If I change selection to "Oracle", I'd like to have the text change to "Rank: 3 out of 3".

My question here is how can I neutralize the slicer's effect on the grand total?

I'd like to show the "count(distinct company)" (in SQL terms) calculated on the entire population, and a similar consideration applies on the ranking.

---

In this example, which column do you want to implement the rank measure on? Where is the population field?

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[David_Efr]

I'd like to display the rank of Company by 'PE Ratio' column.