4 Replies Latest reply on Jan 15, 2013 4:40 AM by Matylda Borchert

    Percent of Total based on underlying data

    Matylda Borchert

      Hi!

      I am trying to calculate Percent of totals. I want to compute the share of adverting (SoA) for a brand, segment or model.

       

      SoA for a brand=  sum TEURO of selected brand/sum TEURO of all brands

      SoA for a segment= sum TEURO of selected segment/sum TEURO of all segments

      SoA for a model= TEURO of selected model/sum TEURO of all models belonging to the “selected” segment

       

      Please see the file attached. This is just an example for a brand with the correct numbers.  The difficulty is to display the information while filtering on brand or segment or model. At the moment, as soon as I start filtering, for example Brand A, I receive 100% which of course makes sense, however this is not what I want to display. I need that the denominator calculates the total based on the underlying data and not on the selected values (shown in the table).

       

      In my example, I want my visualization to look like this:

       

      SoA X% (X being calculated depending on filtered data) 

                                                                                

      For example: Filter: Brand (A), Segment (All), Model (All)

      SoA for Brand A: 2011= 48% and 2012= 48,48%

       

      or

       

      Filter: Brand (All), Segment (X), Model (All)

      SoA for Segment X: 2011= 57,66% and 2012= 59,35%

       

      Is there a way to solve this? Thanks in advance. Matylda

        • 1. Re: Percent of Total based on underlying data
          Robin Kennedy

          Matylda,

           

          Your percent of total is what's called a Table Calculation. Table calcs are performed on the Tableau Table, AFTER all other calculations and filters have been completed so when you are applying a filter on Brand, the table calc percent of total only knows about that 1 brand so will always return 100%

           

          One way round this is to also make your filters Table Calculations - thus the percent of total applies to the whole data table and then it filters just for your selected brand. To do this, you need to create some calculated fields for each of your dimensions you want to have a filter on. For example, the formula for the Brand Filter should be

           

          LOOKUP(MIN([Brand]),0)

           

          This new field is a table calculation. You can then use this as your quick filter instead of the Brand dimension itself.

          • 2. Re: Percent of Total based on underlying data
            Matylda Borchert

            Robin,

             

            thanks for your quick response. I have created the filter variables as you advised me. Unfortunately, the created filter variables do not work if I put them all together on one page. I made a detour and created the calculations for each filter variable on each page and connected them to a dashboard.

            The problem is that at the end in my Dashboard  the views do not share filters, for example: “SoA Brand” works exclusively with “Brand Filter”.

             

            My goal is to create a dashboard where all views react on brand, segment and model filter.  Please see the ppt attached.

             

            Examples:

            1. If I select Brand A, I see the SoA of Brand A  (KPI view) , also the Segment Mix for brand A (Segment Mix view) and the SoA for brand A for each month ( SoA view).

            2. If I select Segment X and Brand A, I see the SoA of Brand A in Segment X  (KPI view) , also the Segment Mix for brand A in Segment X (Segment Mix view) and the SoA for brand A in Segment X for each month ( SoA view).

             

            Cheers, Matylda

            • 3. Re: Percent of Total based on underlying data
              Robin Kennedy

              Hmm.. that's a bnit more complex. What I did was a bit of a quick fix for a single view.

               

              To accomplish what you're trying to achieve in a dashboard, I would probably do a combination of duplicating the data (for my total values, as described here: http://kb.tableausoftware.com/articles/knowledgebase/summary-and-detail-60) and making use of Dashboard Action Filters, rather than quick filters, so you can click on a brand name in one view and it updates the other charts in the other views. More info on that here: http://downloads.tableausoftware.com/quickstart/feature-guides/actions_filter.pdf

               

              I have attached a mock up of what I mean in the attached.

               

              Best of luck!

              • 4. Re: Percent of Total based on underlying data
                Matylda Borchert

                Hey Robin!

                This is brilliant, thanks so much!