1 Reply Latest reply on Apr 18, 2016 9:42 AM by Ashish Singh

    Calculated Field: How to split total amount 50/50 between two categories

    Joelle Clemons

      Greetings!

       

      I have a calculated field already setup in an existing dashboard. It buckets out money raised for certain groups of funds based on the categories they fall into. It's just an ELSEIF ENDSWITH statement. The problem I have is some of these funds should be split 50/50 between two categories with half of the total amount being counted in one group, and half in the other. I'd like to find a way to write this into the existing calc field if possible. Does anyone know of a way to do that?

       

      Thank you!

      Joelle

       

      IF ENDSWITH ([Designation], '1234') THEN 'Fund Group 1'

      ELSEIF ENDSWITH ([Designation], '5678') THEN 'Fund Group 2'

      ELSEIF ENDSWITH ([Designation], '4321') THEN 'Fund Group 3'

      ELSE 'Other Fund Group'

      END

        • 1. Re: Calculated Field: How to split total amount 50/50 between two categories
          Ashish Singh

          Joelle Clemons ,

          It somewhat also depends upon after calculating this field, how do you want to 'display' it in the view.

          Here is something I am attempting, hope this works for you

           

          Problem

          There is a group named group_1 which has raised amount 2x for any given period (this information is coming from day to day transactions table)

          Also, group_1 falls into 2 categories cat_1 and cat_2 (and this information is stored somewhere in the master data)

          Objective

          In the group_1, each category cat_1 and cat_2 should show amount raised x even if the amount is not being raised against any particular category but for the whole group as one.

           

          Concept

          • If somehow I could 'group by' my view by groups
          • And count distinct(categories) in each of those groups
          • And divide the amount raised by group by number of categories
          • Anf 'group by' that amount by categories and show it

           

          Solution

          There would a little data prep before being able to build the view

          Data should be in the following format

          DataPrep.PNG

          group_01 has raised $30K and group_02 has raised $45K but we have to use duplicate that amount

          Load the data in Tableau and write following two calculations

           

          amtRaisedbyGroup

          { FIXED [Group]: AVG([Amount Raised])}

           

           

          amtRaisedbyCat

          AVG({ FIXED [Group],[Catergories]:

              AVG([amtRaisedbyGroup] /

              { FIXED [Group]: COUNTD([Catergories])})

          })

           

          This way you can calculate both : amount by group, and average amount per category in each group

           

          To understand how does this exactly work, you will require learning Level of Details topic, and lots of material is available online on that.

           

          Hope this works. Please let me know if there is some problem.

          P.S. I have created the .tabx in Tableau 9.3 and wouldn't open in older Tableau versions.

           

          Cheers!

          Ashish