1 Reply Latest reply on Oct 31, 2016 7:48 AM by Carl Slifer

    Summing distinct values - method that is robust to grouping

    Robert Gledhill

      I am looking to sum two distinct values, due to grouping two data

       

       

      | Account | country         |sales|

      |alpha      |Japan            |£10|

      |beta        | Japan           |£10|

      |gamma   |Australia        | £12|

      |delta       | New Zealand| £14|

      |epsilon    | Malaysia       | £5  |

       

      |Country         |Headcount|

      |Japan            |      8         |

      |New Zealand|      7         |

      |Australia       |      5         |

      |Malaysia       |      9         |

       

       

       

      I want to find a way to calculate total sales divided by total headcount.

      To use Japan as an example I would want it to say (10+10)/8 = 2.5

      If I grouped Australia and New Zealand I would want it to say (12+14)/(7+5) = 2.17

       

      I have seen others suggesting {FIXED [Sales] : MIN([Sales])} as a solution - this method works for me - however after I group the countries I can't control whether that calculation method works at the country or country group level via the below method.

        • 1. Re: Summing distinct values - method that is robust to grouping
          Carl Slifer

          Hi Robert,

           

          It sounds like this data is from two datasources is that correct? In the top left you have two database cylinders one of which has account, country, and sales fields and the other which has country and headcount as fields?  I do not think this has anything to do with distinct values.

           

          I think all you need is WINDOW_SUM(SUM(Sales)) / WINDOW_SUM(SUM([DataSource2.Headcount])) 

          You will also need to use blending at the country level and on the primary datasource define your groups and then set this table calculation to compute using country.

           

          In Tableau 10 you can define custom regions geographically and remove the country level from view. This allows the calculation to work and still see both Aus/NZ at once and would remove the need for the table calc

           

          Best Regards,

          Carl Slifer

          InterWorks