6 Replies Latest reply on Jan 24, 2019 5:53 PM by Ankur Gupta

    Calculate % of cells across the row with value greater than 5

    Anita Lee

      The second row of each store's data is a binary yes (1) or no (1) for whether each value in the database is greater than 5. How do I calculate the grand total and then divide by the number of days present in the view?

      Capture19.JPG

       

      This is what my calculation was to get to the binary: IF {INCLUDE [store_name] : zn(countd([merchant_sku]))} > 5 THEN 1 ELSE 0 END

        • 1. Re: Calculate % of cells across the row with value greater than 5
          Shinichiro Murakami

          Could you please attach sample data as twbx, not only the picture.

           

          Thanks,

          Shin

          • 3. Re: Calculate % of cells across the row with value greater than 5
            Daniel Lawrence

            Anita,

             

              You have a few things going on here.

             

            1) Your [Days] are formatted as Date & Time rather than Date.  You may have a purpose for the time element for other reasons so i would suggest either splitting out the time to a new dimension or copying [Days] and making one of the copies formatting as Date only.  This is what I did in the attached solution to deal with distinct days.

             

            2) Unfortunately, Tableau's built in row/column totals are only useful for simple aggregations.  When the calculations become complex, not only are the grand totals often calculated in a way that the user is not expecting but it often disallows other functions to exist in tandem with them.  Such examples are index() or rank().

            This is true with [Produce Scans] in your first row as you can see that the row totals do not add up to the individual days summed together.  This is because the Count Distinct is being extended to the whole data set BEFORE it is being broken up by day.  This means that a value that shows up on 1/6/19 will not be counted on 1/7/19 if it shows again because it is not distinct over the whole data set.  But if you change this level of granularity to be only by day, then it will look over the span of a day and find all the distinct values.  This requires a little reformatting of the user's understanding and expectation, unfortunately.

             

            To change this, I put in the LOD function, [Distinct Count of Merchant Sku] which only looks on a daily basis for distinct counts per store and then the built in row total recognizes the values that we would assume it should be aggregating.  

             

            For the grand total divided by number of days, I put that in another worksheet with the associated calculation [Grand Total divided by # of days] which takes the grand total that the built in Tableau row total computes (aka sum([Distinct Count of Merchant Sku]) and divide it by the distinct count of days.

             

            If you want to combine the two seamlessly you can experiment with lots of different options on the dashboard such as hiding the store names on the grand total worksheet and making it smaller to fit right next to the day breakdown sheet.

            1 of 1 people found this helpful
            • 4. Re: Calculate % of cells across the row with value greater than 5
              Anita Lee

              Thank you for the thorough explanation! I didn't realize date could be different from date and time, or that the count distinct was working across the entire data set.

              • 5. Re: Calculate % of cells across the row with value greater than 5
                Daniel Lawrence

                You're welcome.  Tableau has lots of little things like this that are internally consistent to its way of approaching data but that are not necessarily as intuitive to us humans.  Work with Tableau long enough (and post on the forum!) and you will eventually come across many of these.

                • 6. Re: Calculate % of cells across the row with value greater than 5
                  Ankur Gupta

                  I am attaching the file. I have two asks only which i have included in dashboard as well.