4 Replies Latest reply on Jun 13, 2017 6:26 AM by Stephen Gibbons

    Calculation Contexts - for each dimension (not displayed)

    Stephen Gibbons

      Hi. I need to design income bandings for individual clients. For example -

       

      Client A - £130,000

      Client B - £75,000

      Client C - £300,000

      Client D - £750,000

      Client E - £30,000

      Client F - £10,000

      Client G - £400,000

       

      I then need to use the following banding range (bold) as a dimension with the count of clients and sum of income for each band as 3 columns illustrated below (the Client dimension will be absent).

       

      1. >£500,000 / 1 / £750,000

      2. £250,000 to £500,000 / 2 / £700,000

      3. £100,000 to £250,000 / 1 / £130,000

      4. £50,000 to £100,000 / 1 / £75,000

      5. <£50,000 / 2 / £40,000

       

      The problem I am having is finding a function that calculates the income for each individual client rather than the sum of income for a given band, and then putting it in the correct band. If you take the above dataset, the sum of income from Client C and Client G would push them into the top bracket, but would be incorrect as the individual client income for those 2 clients is below £500,000.

       

      (This can be done easily in BusinessObjects by using 'Foreach' in the formula - I need the equivalent in Tableau). Hope someone can help! Thanks.

       

      (Apologies for not attaching a workbook - cannot do as company-sensitive data)

       

      (Tableau 10.2)