1 Reply Latest reply on Apr 24, 2016 7:11 PM by Manideep Bhattacharyya

    Calculate percentage of the table total in a filtered visualization

    Eduardo FCA

      Hello guys,

       

      I’m new to Tableau and i don’t even know if this is the right place to ask, or if it's a very simple question, but i would like to know how to make a few calculations that I do easily on Excel, but I could not be able to do in Tableau. I couldn't find any similar topics in this forum.

       

      I work in a retail company that sells a lot of different products, we categorize the products in a certain way, not exactly but something like this: Category > SubCategory1 > SubCategory2 > Product

       

      I want to be able to see the following about any Product:

      % of the Product of the Total Revenue in a specific month

      % of the Product of the Revenue of the Category in which this Product is in in a specific month

       

      When I’m doing this kind of calculations in Excel, I use conditional formulas and find the answer very easily (IF, SUMIF, COUNT.IF, etc.), I’ve attached a workbook to show an example.

       

      My current problem with Tableau is that whenever I want to show an specific product in a visualization, I have to use filters to show that product, but when I do that, the entire visualization is filtered and I cannot do the calculations above properly, because Tableau will calculate the percentage of the total that is already filtered (that will always be 100% if only one product is filtered) and I could not find a way to use the month as a part of the equation either.

       

      **EDIT: I've found part of the solution on the item 14 here: Top 15 LOD Expressions | Tableau Software, i've tried and it worked for the grand total and category total, but i still don't know how to make this calculations work with the months.

       

      Can someone help me understand how can I do something similar that I do in Excel on Tableau?

       

      Sorry about any grammatical errors, English is not my native language.

        • 1. Re: Calculate percentage of the table total in a filtered visualization
          Manideep Bhattacharyya

          Hello - Welcome to the Tableau Community !

           

          The requirement you have given, can be easily achieved in Tableau. Let me tell you that I am not an excel expert. It took lot of time to digest SUMIFS function. For others also to digest, I am providing the Microsoft help link, this was helpful:

          SUMIFS function - Office Support

           

          But In Tableau, such things can be done very easily through LODs. To cut long story short, please find the screen shots and the LOD expression for "% of the Product of the Total Revenue in a specific month".

          SUM({fixed [Month], [Product]: sum([Revenue])})/SUM({fixed [Month]: sum([Revenue])})

           

           

          For "% of the Product of the Revenue of the Category in which this Product is in in a specific month" - Please find the LOD expression and the chart

          SUM({fixed [Month], [Category], [Product]: sum([Revenue])})/SUM({fixed [Month], [Category] : sum([Revenue])})

           

           

          PS: If this solves your problem, please mark this as correct answer to close this thread.

           

          Thanks,

          Manideep