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

# Sum Distinct to two Levels of Detail

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.

• ###### 1. Re: Sum Distinct to two Levels of Detail

Thomas,

Have you tried:

{FIXED Account ID: MAX(Amount)}

• ###### 2. Re: Sum Distinct to two Levels of Detail

Just test it out and got result below:

Sherzod

• ###### 3. Re: Sum Distinct to two Levels of Detail

I will try it, but I just want to note that I just provided an example of 1 Conversion. There are thousands of conversions, that both have accounts 1 and 2. So I believe I cant just take distinct Ids, because this would only count the amount of 1 conversion.

• ###### 4. Re: Sum Distinct to two Levels of Detail

Thomas,

In that case, you may want to include Conversion ID in front of Account ID so that it will reset for every unique Conversion IDs. Hope I understood your requirements.

• ###### 5. Re: Sum Distinct to two Levels of Detail

How would you include Conversion ID in front of Account ID? Using a nested fixed : statement ? Could you show me the formula?

• ###### 6. Re: Sum Distinct to two Levels of Detail

Sorry for not being specific. I mean: {FIXED Conversion ID, Account ID: MAX(Amount)}

Please try and test it and see if you can get the result you want.

1 of 1 people found this helpful
• ###### 7. Re: Sum Distinct to two Levels of Detail

Thomas,

Just saw your updated sample. Just tested updated calculated field i mentioned and seems to be working for me, but you let me know if that is what you want.