2 Replies Latest reply on Jul 18, 2013 11:43 AM by Jonathan Drummey

    Create an "other" field based on a dynamic calculation




      I am hoping to find a simple solution to the following issue:


      I have a large set of ~50 products, which varies from month to month. I am visualizing them in a pie chart (I know, I know), by sum of kWh energy savings. I am using a quick table calculation to represent the slices as percent of total kWh energy savings.


      It looks like this:

      Pie Example - Other Field.dib.bmp

      I would like to group all categories that are under 2% of total kWh savings into an "other" field. Here's the catch - I want to do it dynamically, so that I can change my filters (ie the month, the region, etc), and have my "other" field automatically recalculate...I also want it to be dynamic for when new data comes in, when categories are added to or fall off my radar, or when I want to look at other specific criteria.


      It seems like this should be simple, but I'm stumped. I could do this easily if I wanted to create an "other" field based on a fixed number, or a hierarchy set, etc. It's the fact that I want to do it dynamically by percent of total that seems to stump me (or tableau).


      My initial solution was to rename the product name as "other" based on a calculation, ie if the percentage of sum of total was less than 2% - it failed because I was mixing aggregated and non-aggregated fields.

      My failed attempt:

      if SUM([Total Reported Gross kWh]) / TOTAL(SUM([Total Reported Gross kWh]))>.02 then [Measure Name] else 'Other' end


      Any suggestions? I cannot share the original data book, but I could possibly make an analogous mock up if necessary.



        • 1. Re: Create an "other" field based on a dynamic calculation
          Alex Kerin

          An interesting issue - so far I can create the "Other", but cannot group them as one pie piece:



          • 2. Re: Re: Create an "other" field based on a dynamic calculation
            Jonathan Drummey

            To get all the pie segments to be in the right order, sort on sum/sales/descending. However, that will still have a pie segment for each State and the labels won't look right. I used the Top N and Other techniques in the attached.


            Also, since I couldn't resist, I put together bar chart and treemap versions of this.




            PS: The reason why we need the sort is that Tableau will draw a mark for each "row" of data returned from the data source (after all filtering occurs), and for a pie chart with State in the level of detail that means Tableau is going to draw 50 segments, drawn from 12am clockwise. When we sort based on sum/sales/descending that works well with the % of total calc.


            The Top N and Other solution uses a table calculation to only return the state meeting the threshold, and Other for the 1st state that doesn't meet the threshold, and Null for everything else. Then we can filter for non-Null values and only draw N+1 marks, and the sort works fine.