5 Replies Latest reply on Aug 23, 2018 9:06 AM by Danny McKenna

    Table Subtotal with ZN Padding

    Danny McKenna

      I have a bit of an obscure question for tableau, so any help is appreciated! I have a table that lists values from groups over a time span, with the groups on the rows, and year/quarter/month on the columns, and the value in the table is summed. I have the sub totals set to average, so we can compare monthly expenses, along with quarterly averages (as the value can change quite a bit month to month, we've found it handy to have both monthly sums, and quarterly averages)

      The issues start to pop up when there’s a blank/null value in the table, the quarterly averages don’t consider that column as a value that it should use in the average denominator (I’ve attached a work book, check out the normal tab, Q1 2018, group B or D to get an example of how the quarterly average is an average of the 2 values, and not 3 months). So to fix this, I replaced sum(value) with zn(lookup(sum(value),0)), to replace the table null values with a 0 (see the with padding of zero’s tab in the attached example). This seems to work on everything except the quarterly averages, which now are defaulted to sums, and can’t be changed.

      If someone has a tip/trick/way to get the average subtotal column to either use all the values (even if null) or how to change the subtotal column to average when using the zn function, I would be profoundly appreciative!

      Thanks!