6 Replies Latest reply on Feb 2, 2017 11:42 AM by Sherzodbek Ibragimov

    Sum Distinct to two Levels of Detail

    Thomas Karbe

      My data has the following format:

       

      Conversions IDAccount ID
      Amount
      Category of Conversion
      11100Airline
      12200Airline
      11100Hotel
      12200Hotel
      21300Car
      22400Car

      Each conversion (sale) is related to two accounts. The conversion (sale) had a value of 300$ and was split between 2 accounts. Account 1 received 100$ out of 300$, while Account 2 received 200$ out of 300$. Additionally the conversion also fits into two categories (Airline and Hotel).

       

      My question is: How can I calculate the Distinct Sum of each conversion? In the above example I would be looking for the result of 300$ (100$ + 200$). I would like to take the distinct conversion id (1) , then the account ids that belong to it (1 and 2), and then sum the amounts (100 +200).

       

      At the moment I am just getting a sum of all amounts, which is resulting in 600$.

       

      How could you do this in tableau? To me this seems like two level of details, though I am not sure if this is correct use of the terminology.