2 Replies Latest reply on Oct 2, 2013 2:43 PM by Zac Hilbert

    How does one prevent certain partitioning/addressing of a table calculation from displaying multiple marks

    Zac Hilbert

      I am trying to compare the count distinct of an item to the count distinct of that item at another level of aggregation.  In the end I will want a percentage calculated by using  countd([ID])/total(countd[ID])).  I have the table calculation addressed and partitioned so I am getting the right values for total(countd([ID]); however, the correct partition is causing each cell to contain marks that I believe belong to another cell (though, I understand that those other cells would be part of the table calculation.)

       

      ss1.png

      For example, in the first row of the "m1" column, I only want to see m1_a,5,9.  How do I prevent tableau from putting m2_a, null,9 in this cell? I want it to display the marks as is does before I add the Total Count Distinct field to the marks shelf:

      ss2.png

       

      I've attached a workbook and would appreciate any suggestions.  My current work around was to just pre-calculate the distinct counts in my underlying data and avoid the table calculations altogether.

       

      Thanks,

       

      Zac

        • 1. Re: How does one prevent certain partitioning/addressing of a table calculation from displaying multiple marks
          Jonathan Drummey

          Hi Zac,

           

          This isn't a problem of addressing so much as the arrangement of dimensions and interaction with table calculations causing unwanted densification (in particular domain completion) of results.

           

          When I set up the view with no measures, there are 24 marks in the view (everywhere you can see an Abc):

           

          2013-10-02 15_31_04-Tableau - densification example.png

           

          However, even though there are only 24 marks, with this pill arrangement there are 48 cells.

           

          When I bring a table calc into the view (it doesn't matter what table calc or what the Compute Using is), depending on the Compute Using setting all of those cells can be treated as addressable by Tableau and now show Abc, and there are 48 marks. The general term for this is densification, the particular form here is domain completion.

           

          2013-10-02 15_32_25-Tableau - densification example.png

           

          What was happening in your original view was that based on your table calc Compute Using settings and pill arrangement, the view was densified and you ended up with the extra values.

           

          Note that this is dependent on a set of factors - if we get rid of the table calc pill, densification goes away, as you found. If we change the arrangement of pills, the densification goes away (see "No densification w/TC, 24 marks) in the attached. Changing the Compute Using won't work, though, because that's not a factor in this case. Changing the Compute Using can work in some cases, but not this one, as far as I can tell, because of the what's needed for addressing and partitioning. (But it can be in other cases, which is part of what is crazy-making about table calculations).

           

          There are a couple of solutions to this:

           

          Final View #1: The pill arrangement (having dimension pills on opposing Row/Column Shelves) is triggering the densification. We can move the m dimension onto the Level of Detail, and replace it with a measure like ATTR(m). MIN() or MAX() would work fine as well, and possibly be faster in some cases. (Thanks to Joe Mako for this trick).

           

          Final View #2: In your data there are two fields that are not needed as dimensions in the view, namely the InTop3 and Rank fields. In that data, the combination of class, m, and Entity is all that is needed. So for this view, I turned both InTop3 and Rank into measures, so they are no longer available for addressing or partitioning. The Compute Using of the table calc can now be on Entity and it works.

           

          In general, I try to keep the number of dimensions in a view to the minimum needed for the table calcs to work, and make everything else an aggregate. This will generally be faster in the datasource as well. Doing this would have avoided the problem you ran into in the first place, however based on the goal sometimes I still end up with densification due to pill arrangement and then using the technique from Final View #1 (put the dimension on the Level of Detail and replace the pill on Rows/Columns with ATTR/MIN/MAX) is necessary.

           

          Also, in your original view you had At the Level on Entity for the tcd calc. Because Entity was at the bottom of the Addressing list, this is the same as choosing the default Deepest for At the Level (it's an unfortunate aspect of Tableau's UI). I try to not do anything with setting At the Level until I absolutely have to because table calc settings are confusing enough without having a setting that doesn't do anything, but then might if I rearranged something else.

           

          Hope this helps!

           

          Jonathan

           

          [Updated 2013-10-03 w/some corrections about densification].