1 Reply Latest reply on Aug 8, 2013 5:03 AM by Jim Wahl

    Usage Calculation

    JR Oakley

      I've attached an example of the data I'm working with.  I'd like to calculate a percentage by dividing the usage of one 'grade' by another 'grade'.  I would also like to group (not sure if set is my best approach) the two yellow grades together so they share a common alias.  Since my usage by grade isn't broken down into unique measures I do not know how to group and calculate.

        • 1. Re: Usage Calculation
          Jim Wahl
          I'd like to calculate a percentage by dividing the usage of one 'grade' by another 'grade'.

          Since all of your grades are in the dimension Grade, you'll need to create a calculated field for each type:

          Def =

          IF [Grade] == "DEF" THEN [Usage] END

           

          Bio =

          IF [Grade] == "Bio" THEN [Usage] END

           

          Now you can divide these as aggregates.

          Def / Bio =

          SUM(Def) / SUM(Bio)

           

          The SUM() function makes these aggregates. Without the aggregation, there's no way for Tableau to now what individual Def and Bio rows you want. If you segment your view by Site #, however, the aggregates will be calculated for each segment.

           

          I would also like to group (not sure if set is my best approach) the two yellow grades together so they share a common alias.

          You could again create a calculated field

          Group =

          IF CONTAINS(UPPER([Grade]), "BIO") THEN "BIO"

          ELSEIF CONTAINS(UPPER[Grade], "USLD") THEN "USLD"

          END

           

          Note that I've used CONTAINS() and UPPER() to provide some robustness against variations of the names and capitalization (you may want to do this in the first part as well), but you could also use an exact match IF [Grade] == "BIO" THEN "BIO" END if you're comfortable that your data is clean.

           

          Jim