1 Reply Latest reply on Nov 12, 2012 11:46 AM by Jonathan Drummey

    Weighted Calculation for upper hierarchy

    Ajit Kumar

      Hi,

       

      I have two country (A and B) data for three measures (OSA, SL, TO). By aggregating these two countries I have to calculate the total number.

      Below is the formula by which I need to calculate the Total-

       

      Total = (((((A’s OSA/A’s SL))*100)*A’s TO) + ((((B’s OSA/B’s SL))*100)*B’s TO))/ (A’s T/O+B’s T/O)

       

      I have attached the data file and linked tableau workbook where I was trying to achive this.

      I am writting the formula in Tableau is -

      Total = Sum(((OSA/SL)*100)*T/O) / Sum(T/O)

      but I am getting 0.0% as answer which is not correct.

       

      Please help me to achieve this in Tableau.

        • 1. Re: Weighted Calculation for upper hierarchy
          Jonathan Drummey

          Hi,

           

          You haven't specified the aggregations at a sufficient level of detail for me to help. There are a few issues: The calc you've specified in the Total calc has a row level calc embedded within the aggregate SUM(). This will return Null for a number of rows because SL is 0 for many rows. Also, since Target is the same for every row, I'm not sure what kind of aggregation you are wanting there, and the TO does varies across rows but is the same for many. Are you sure you're wanting SUM() there and not a different aggregation?

           

          Separately, Country will need to be in the Level of Detail in the view in order to generate a calculation that works across the countries.

           

          The way I'd go about this is to:

           

          0) Set up Country on Rows

          1) Create a child calc or calcs that work(s) at the Country level in a crosstab.

          2) Create a parent calc that uses a table calculation to generate the cross-country comparison and explicitly set the Compute Using.

          3) Modify the parent calc so that it returns the correct result for only one country.

          4) Drag Country off Rows to the Level of Detail Shelf.

           

          Jonathan