2 Replies Latest reply on Apr 1, 2016 7:32 AM by . Balfour

    "Fixed" Calculation Difficulties

    . Balfour

      Hi everyone,

       

      Sorry for the vague title - but I didn't know exactly how to name this thread, but it appears I'm having some issues with the "Fixed" Calculation feature in the create a calculation interface.

       

      As things stand, I currently have the following two calculations, that enable me to show the % of revenue that each country or product contribute towards the overall % figure.

       

      These two calculations are:

       

      TotalRevenue: {FIXED [Fiscal Quarter],[Country]: SUM([Sales Pack Net $])}

       

      %Rev: SUM([Net $]) / SUM([TotalRevenue]) * 100

       

      These two calculations give me the following view (which works perfectly) and allows me to view the % of each country's contribution to the overall revenue figure :

       

      eg1.png

       

      However, an issue arises when I attempt  to add (over even replace instead of Country) my Products - in this case called Tech Split - field to the same calculation.

       

      I use the same formula, the time with Tech Split in the field instead of Country:

       

      {FIXED [Fiscal Quarter],[Tech Split]: SUM([Sales Pack Net $])}

       

       

      %Rev: SUM([Net $]) / SUM([TotalRevenue]) * 100

       

      And, instead of adjusting to 100% like the initial Graph, the following occurs - and the calculation does not adjust the fields to 100%, rather a variable % between 100-130:

       

      eg2.png

       

      I have no idea what I'm doing wrong - but as things stand I am unable to offer my clients the ability to filter between my Tech Splits at a % level due to this error.

       

       

      I will also point out that the Tech Split field is different from the dimension that is being used to display the splits in colour you see in the graphs - so it isn't related to that.

       

      Tech Split is the only field I cannot calculate a % for based on these calculations, and it perhaps the most vital one.

       

      I hope there is a simple solution out there,

       

      Thanks in advance, Ross.

        • 1. Re: "Fixed" Calculation Difficulties
          Patrick A Van Der Hyde

          Hello Ross - is it possible to share a workbook with enough data for us to reproduce the issue?  Even a crosstab view might tell us what is going on - right click on the Viz Tab at the bottom of the screen in Tableau and "Duplicate as a Crosstab".  The percent numbers are coming up too high so this is issue is going to relate to something about how Sum(Net $) being greater than Sum(Total Revenue) when this [Tech Split] is used per Quarter .   The crosstab view is a great way to see what the numbers show and to dig into what's going on . 

           

          Patrick

          • 2. Re: "Fixed" Calculation Difficulties
            . Balfour

            Hi Patrick,

             

            Thanks for your reply - sadly the data in question is company-sensitive and cannot be shared in the public domain. I can however show the same data on a crosstab, as you can see in the image below.

             

            The only field that this occurs in, is when Tech Split is brought into the calculation. Any other dimension called in the "Total Revenue" figure enables the area graphs to perfectly align to 100% of the total. The field is no different to any others, which is why I'm quite confused about why this is happening.

             

            Hopefully this crosstab can help you out, as you can see the Total is still at 100%, yet the figures that contribute towards the total are in and around 140/150%?

            crosstab1.png

             

            Thanks, Ross.