Show the selected in a sorted barchart

This tip is about ranking your data.

I have a simple sorted barchart here that shows the ranking of some countries on Revenue:

Now I want to give an extra service to the users by highlighting a selected country like this (where Denmark is selected):

To do this I make a user dimension with 2 elements:

First I click add in the User Dimension dialogue:

And then immediately OK (without adding any criteria):


Do the same again (Add followed by OK) - then you have this User Dimension - note I have named the 2 elements Selected and Total:

Now we make one change to the Total element - choose selected and Ignore Dimension on Member:

Now choose the Country Dimension:

What we have now is a User Dimension (holding no criteria), where one element (Selected) will respond to filtering on the Country Dimension and the other element (Total) will ignore this filtering.

The User Dimension is saved with the name Selected Country vs Total.

In a crosstable we put country on the vertical axis and our user dimension on the horizontal axis:

This is what the table looks like when no countries are selected:

And this is what is looks like if you have selected Denmark as a filter:

So now we just need to things:

1. Hide the first column through visibility

2. Add a color agent with a condition like this:

count(d1,0,m1)=1 and count(d1,all,m1)=1

Count function returns 1 if there is a number in the cell, so count(d1,0,m1)=1 checks if there is a number in the first column.

Count(d1,all,m1)=1 checks that something has been selected - remember when nothing is selected the first column will contain the same numbers as column 2.

Now you just need to sort the data in descending order on the 2nd column and change the crosstab to a barchart - and that's it.

You could even add a measure selector to the criteria bar and then you can rank on any measure the end users prefer!

I think there are a number of usecases for this Selected vs Total User Dimension - please let me know if you have some great examples of using this logic :-)

Hope it makes sense.








1 comment
  • This is great to see the dynamic changes but also the "Totals" or the overall picture, thank you for sharing this!


Please sign in to leave a comment.

Didn't find what you were looking for?

New post