4 Replies Latest reply on May 31, 2016 10:54 PM by Alex Ixeras

    Counting Unique Grand Total

    Alex Ixeras



      I have a table with Customer IDs and interactions of that customer on a daily basis. I want to be able to count the number of interactions in a given time frame and show how many counts of those interactions I've had. The attached screenshots might explain that in better detail than I can describe here.

      Tableau - Interactions - Concept.png

      I have set up Tableau the following way, representing step 1:

      Tableau - Interactions Grand Total.png


      How do I get to count the unique grand totals for step 2?


      I have:

      • Analysis > Aggregate Measures : On
      • Analysis > Totals > Show row grand totals : On
      • Analysis > Totals > Show column grand totals : On


      And this is what I have for the count mark:

      Count Mark.png



        • 1. Re: Counting Unique Grand Total
          Andrew Watson

          Look at LOD calculations, they might do it for you. {FIXED [CustomerId]: COUNT([Interaction Code])}


          That formula should be usable to create 2 and 3. The date field would need to be a context filter.


          This link will help: Should I use FIXED or INCLUDE/EXCLUDE?

          • 2. Re: Counting Unique Grand Total
            Alex Ixeras

            Thanks, Andrew. Two further questions:

            1. I guess I need to put the LOD calculation in the rows. But what do I put in the columns?
              If I do that and leave the Date in the columns, I get the following at the moment. No numbers.

            2. I named the LOD calculation "Calculate Unique Interactions". Should I then select Sum of "Calculate Unique Interactions" or should it be Count (Distinct)?


            The table behind looks like this:

            Interaction Date   Customer Id   Interaction Code

            01/04/2015         1000705       DI_M

            01/04/2015         1000713       DI_M

            05/04/2015         1000705       DI_M

            11/04/2015         1082489       DI_M

            20/04/2015         1002486       DI_M

            21/04/2015         1188631       DI_M


            Interaction Code 'DI_M' is what I filter on. E.g. Customer Id 1000705 appears twice, so I'd want that to be counted as 2 if I filter for the date range that includes the two dates 01/04/2015 and 05/04/2015.




            • 3. Re: Counting Unique Grand Total
              Andrew Watson

              Based on your provided dataset and creating the chart labeled 3 at the very beginning of this thread I get the following:



              Calculation 1 is the formula: {FIXED [Customer Id]: COUNT([Interaction Code])}


              Adding the filter gives the following:



              Is that what you are looking for?

              • 4. Re: Counting Unique Grand Total
                Alex Ixeras

                Thanks, that's it. I think what I did wrong was that my Interaction Date and Customer Id were a Dimension instead of a Measure and that my LOD was a Measure instead of Dimension. That's how they were in the dataset that I'm working on. When do I need to set which data to Dimension or a Measure?