Using an Exclude LOD to sort on a dimension member within a % of Total table calculation

Lets say I have this view, I have my order history broken down by category using a % of total calculation for each state.

This allows me to answer the question ‘what % of orders does Furniture account for in Michigan’ for instance; indeed I can now answer that question for all states.

StateSalesCategory.png

However, I can’t quickly answer the question ‘which of my states has the greatest contributions of its sales made by the furniture category. My users would have to look state by state and identify the biggest bar, which isn’t as simple as it could be with a sort.

What we need to do instead is create a calculated field that simply returns the % of total for Furniture. We can then take this calculation and use it to sort our states by.

We can do this very easily courtesy of a couple of quick calculated fields.

First of all, lets create a calculation that only returns the Order ID when the line relates to a ‘Furniture’ type sale.

Return OrderId.png

This calculation is very simple, we are simply returning the [Order ID] when the [Category] equals furniture, if it does not then we are returning NULL (NULL values do not contribute as part of the count).

Secondly we need to create a calculated field which works out the total number of Orders, irrespective of the Category type. The ‘irrespective’ comment is key here because it sits in our view, thus we must use an exclude LOD in order for it not to affect our results.

The calculation will look something like this.

exludelod

Now, LOD’s must take place upon an aggregation, thus we will return the count within the calculation.

Finally, we need to create our ratio, i.e. the contribution of furniture orders to our total number of orders.

ratio

Here we are creating a count of our furniture orders, and dividing it by the total count of all orders.

If we take this calculation and drop it on label, we can see that we get the same value for furniture as given by the table calculation, but we get 0 (because there are no furniture orders) for the other categories.

order tooltip.png

Now by right clicking on the state field we can use the sort function, and from the field select our ‘Sort Orders’ calculation, and like magic, our orders are now sorted.

States.png

A bit of tinkering and we can use a parameter which determines which category type we wish to sort by. View and download the viz here and give it a go 🙂

Ben

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s