4 Replies Latest reply on Jan 23, 2017 8:51 AM by Jacob Wineman

    Share Report with Static Denominator

    Jacob Wineman

      Hi everyone.  I have a share report I'm trying to create in Tableau 10.0 for Dollar Sales and I'm not able to get a calculated field to generate the share number I'm looking for.

       

      My goal is to create a dollar share metric across all the Geography dimensions, but instead of basing it on the total of all rows I want to base it on the "All GEO" geography.  I also want to be able to add other dimensions to the rows and have the share numbers update.  So the calculation will need to be dynamic for all dimensions, but hold the dimension that the share is calculated against static.

       

      Has anyone run into a scenario where they have used a calculation like this before?  I have attached a sample file for reference.

       

      Thanks,

       

      Jake

        • 1. Re: Share Report with Static Denominator
          Jamieson Christian

          Jacob,

           

          If I understand your requirements, the "ALL GEO" is a value that is fixed at the MANUFACTURER level.

           

          This can be done with LOD Expressions. I have attached a workbook, but it is in version 10.1.3, which you may not be able to open.

           

          Here is the calculation that I wrote:

           

          And here is what the results look like:

           

          (EDIT: Apologies for the repeated edits. I kept realizing I had over-engineered it and went back in to post revisions.)

          • 2. Re: Share Report with Static Denominator
            Jacob Wineman

            Hi Jamieson,

             

            Thanks for the help.  I think I simplified the problem a little too much.  The LOD calculation gets me part of the way there, but I need to be able to add additional dimensions in beyond the manufacturer, such as Brand, Size, Segment, etc.  Fixing the dollar sales on Manufacturer prevents me from doing this.  I simplified your calculation, which gets me the correct % at any level for 'All Geo', but it filters out percentages for any other geographies.  Let me know if this doesn't make sense.

             

              Thank you.

            • 3. Re: Share Report with Static Denominator
              Jamieson Christian

              Jacob,

               

              There are two ways you can handle other scenarios:

               

              (1) Build separate FIXED LOD calculations for the other levels of detail that you need to support.

               

              For example:

               

              [% of ALL GEO for Brand]

               

              [DOLLAR SALES] / { FIXED [BRAND] : SUM( IF [GEOGRAPHY] = 'ALL GEO' THEN [DOLLAR SALES] END ) }

               

              (2) Build an EXCLUDE LOD calculation instead. This makes the assumption that [GEOGRAPHY] will be a dimension for any of the report contexts (a safe assumption, I'm guessing).

               

              [% of ALL GEO exclude LOD]

               

              [DOLLAR SALES] / { EXCLUDE [GEOGRAPHY] : SUM( IF [GEOGRAPHY] = 'ALL GEO' THEN [DOLLAR SALES] END ) }

               

              The latter is illustrated in the attached, revised workbook. If you use it in your larger data set, you should be able to replace [MANUFACTURER (group)] on the Rows shelf with any of your other dimensions, and it will still properly compare % of ALL GEO.

              • 4. Re: Share Report with Static Denominator
                Jacob Wineman

                Jamieson,

                 

                Scenario 2 was what i needed.  Thanks so much for your help!

                 

                Jake

                1 of 1 people found this helpful