2 Replies Latest reply on Jan 11, 2019 11:42 AM by Yao Lu

    How to calculate from different dimensions

    Yao Lu

      Hi Everyone,

      I have some problem of calculating data from different table, the following is the introduction:

      1. Two dataset: one is benefit value by benefit type,Quarter and country; the other one is the head count by country and Quarter.

      2. I have blended two datasets by country and quarter,  and calculate the average Headcount per year.

       

      I want to have the result of the benefit per headcount of every benefit type and every year. For example, the benefit value of commute(benefit type) in 2015 is divided by avg headcount in 2015. How could do this in tableau?

       

      Thanks for in advance for your help.

       

      --Yao

        • 1. Re: How to calculate from different dimensions
          Patrick Van Der Hyde

          Hello Yao,

           

          I want to preface this - This is not an easy solution and depends on your knowledge of Table Calculation functions to implement.  That said - with some time and careful learning, you too can master these if needed.

           

          I would first suggest trying to adjust the raw data into a single data source or one that could be joined.

           

          Here is a working solution (workbook attached)

           

           

          The calculations created -  A new benefit Value field - if first()=0 then window_sum(sum([Benefit Value])) end

          A new windows calc to avg HC in the HC data - window_avg(sum([HC]))

          A new TC HC Avg field in the primary data source -  if first()=0 then [HC (sample)].[windows calc for Avg HC] end

          A new Year field -  datepart('year',[FY])  that has been made into a Dimension.

           

          Each of these has been defined uniquely to utilize the FY field as a dimension to include when determining the Avg since the average is across all of the values of FY (a series of dates) within a single Year.

           

          Each of the table calculations utilizes IF First()=0 then ....  end - because the calculations will return a result for each instance of FY per cell and we only want one instance returned.

           

          Note that any additional dimensions added to the view will impact the calculation of the Table Calculations.  You may want to invest time in learning details of Table Calculations in Tableau if you do not have much familiarity with them. 

           

          Best of luck and if you found this to resolve your issues here, please mark the post correct.

           

          Thank you

           

          Patrick

          Tableau Community Manager

          1 of 1 people found this helpful
          • 2. Re: How to calculate from different dimensions
            Yao Lu

            Hi Patrick,

            Thanks for your help very much. I will learn it carefully.

             

            Thanks again,

            Yao