7 Replies Latest reply on Nov 26, 2013 8:08 AM by Jonathan Drummey

    Count Values In A Calculated Field And Get Percentage of Overall Total

    jesse.mills

      I have a scatter plot that I broke up in quadrants.  Quadrant 1 include all positive x,y coordinates, Quadrant 2 includes all -x, y coordinates, Quadrant 3 includes all -x, -y coordinates, and Quadrant 4 includes all x,-y coordinates.  I want to create a calculated field that calculates the percentage of points that fall into Quadrant 1, and then color the points in quadrant 1 based on that calculation.

       

      It's essentially this formula:

       

      IF point = quadrant 1 THEN count(all points in quadrant 1)/count(all points in quadrant 1+2+3+4)

      elseif point = quadrant 2 then count(all points in quadrant 2)/count(all points in quadrant 1+2+3+4)

      etc etc..

       

      But I can't figure out the correct way to do that.


      Color the points based on that calculation.

       

      So, say for example there are 4 points in quadrant 1, 6 in quadrant 2, 8 in quadrant 3, and 31 in quadrant 4.  I'd want all the points in quadrant 1 to be colored 4/(4+6+8+31). 

       

      My actual workbook has a good deal more quadrants and points, but that's the basic gist of what I'm trying to do.

        • 1. Re: Count Values In A Calculated Field And Get Percentage of Overall Total
          Ramon Martinez

          Hi Jesse,

           

          Create a calculated field "Quarant"  to determine points in every quadrant with formula:

           

          If [field_x]<0 and [field_y]<0 then "Quadrant III"
          Elseif  [field_x]<0 and [field_y]>=0 then "Quadrant II"
          Elseif  [field_x]>=0 and [field_y]<0 then "Quadrant IV"
          Else "Quadrant I"
          End
          

          In your scatter plot view, having [field_x] in Columns shelf and [field_y] in Rows shelf, add the calculated field Quadrant to Color Mark. As a result, dots will be colored according to the quadrant

           

          To determine the proportion of points per quadrants follow the steps:

          1. create a new worksheet
          2. add the calculated field Quadrant to Rows shelf
          3. add Number of records to Label mark
          4. go to Menu Analysis>Percentage of>Column

           

          As result you will get the % of points per quadrant

           

          I hope this help

           

          Best,

          Ramon

          • 2. Re: Count Values In A Calculated Field And Get Percentage of Overall Total
            jesse.mills

            Thanks, but that's not exactly what I'm looking for.  I'd like to be able to determine the percentage of points per quadrant on the scatter plot view and color accordingly within the same worksheet as opposed to have to create a new one. 

            • 3. Re: Count Values In A Calculated Field And Get Percentage of Overall Total
              Ramon Martinez

              Hi Jesse,

               

              I'm understanding that you need to color points per quadrant, I mean, all points in a quadrant will have the same color. Is that correct?

               

              If I'm understanding well that part of your requirement, you can use the formula I provided for color code the points in you scatter plot.

               

              To overlay the proportion of points per quadrant on the scatter plot we need to do a workaround, creating a worksheet per quadrant showing the proportion of points per specific quadrant. Then create a dashboard with the scatter plot and overlapping those four worksheets with the % points per quadrant 

               

              To calculate the proportion of points per quadrant, a calculated field should be created with the formula:

               

              COUNTD([Number of Records])/TOTAL(COUNTD([Number of Records])

               

              If you post your workbook with sample data, it would be easier for any helper in the community to provide a solution to your requirement.

               

              Best,

              Ramon

              • 4. Re: Re: Count Values In A Calculated Field And Get Percentage of Overall Total
                Jonathan Drummey

                Hi Ramon,

                 

                I don't think the COUNTD([Number of Records])/TOTAL(COUNTD([Number of Records])) will work, because some sort of record ID will need to be on the level of detail so the COUNTD([Number of Records]) will alway be 1.

                 

                Hi Jesse,

                 

                I prepared a couple of alternatives, one uses the Quadrant determined by a row-level calculation based on X and Y, so the Quadrant is a dimension. Then one table calculation computes the number of points in the quadrant, a second computes the total number of points, and a third returns the % of total. These use nested compute usings and color the marks based on the quadrant's % of total:

                 

                Screen Shot 2013-11-24 at 8.08.45 PM.PNG.png

                 

                 

                The second worksheet determines the Quadrant via a measure, this gets quite a bit more complicated because although we can turn off Ignore in Table Calculations to partition a table calculate on a discrete measure, there are only certain cases when the pill arrangement is just right that we can address on the measure, and that's not usually the case for a scatterplot. So I used a duplicated data source with a dummy blend dimension to do a COUNTD([ID]) on the secondary to get the total number of records, and the same # of Points in Quadrant calc as the first view. Note that the dummy blend dimension has to be in the view for this to work, and if you're doing any filtering of the views then you'll need to be careful to make sure both the primary and secondary get filtered by including those dimensions in the view and making sure they are turned on as linking dimensions.

                 

                Finally, as Ramon said, this would have been easier to put together if we had some sample data and/or a mock-up of your desired view. If you have further questions from here, I suggest that you do that, so we can better help you get the results that you need.

                 

                Hope this helps!

                 

                Jonathan

                • 6. Re: Count Values In A Calculated Field And Get Percentage of Overall Total
                  jesse.mills

                  Jonathan,

                   

                  Your workbook looks like it answers my question.  I can't entirely follow along, however.  I've attached a sample of my data.  Essentially I want to color based on the percentage of each "Group" as a Dimension.  It looks like your workbook does that, but I can't follow the steps you did to get there.

                   

                  Thanks for all the help here!

                  • 7. Re: Re: Count Values In A Calculated Field And Get Percentage of Overall Total
                    Jonathan Drummey

                    Hi Jesse,

                     

                    You've asked a different question here... In your original question, you wanted to color the marks based on the quadrant, which was derived from the data. Now you're asking to color by the % of total in the Group, which is pre-calculated.

                     

                    Also, in the example I set up there was a unique row ID so that way we could no matter what get to an accurate number of records. What makes for a unique row ID in your provided data is the combination of Group, X, and Y, and I don't know whether that would be accurate in the long run. You set up your view using the Group on Color with X and Y as Dimensions, so that gets you 909 marks corresponding to the 909 rows in your data, however if multiple rows had the same Group, X, and Y then you'd end up with the same number of marks, even though there would be more rows in the data. This does not affect a computation based on summing the number of records, but it would affect computation based on something else like the size of the partition (like my original solutions). In addition, dimensions are used for addressing and partitioning table calculations, so using X and Y as dimensions means that table calculations have to be configured to handle them and return accurate results.

                     

                    Here's how I set up the "workout" view:

                     

                    1. Created a validation worksheet. When working with table calculations, I find it incredibly helpful to start with a text table so I can validate all the exact values before going to the final view. I start by putting all the dimensions for the view on the Rows Shelf, so I created a worksheet with Group, X, and Y on Rows. Make X and Y both discrete. When using table calculations I order the pills on the Shelf with partitioning dimensions to the left, addressing dimensions to the right. In this case, we know we're going to need to sum up all the records for each Group, so that goes on the left.
                    2. Brought SUM(Number of Records) onto Columns. Since the axis only goes from 0-2, that means there's only one record for each combination of Group, X, and Y. A visual scan confirms this, and this is useful to identify the level of detail of the data.
                    3. Moved SUM(Number of Records) to the Text Shelf.
                    4. Turned on Subtotals for the Group pill.
                    5. Used Analysis->Totals->Show Column Grand Totals to turn on grand totals.
                    6. Created a "# of Records in Group" calc with the formula TOTAL(SUM(Number of Records)). Brought that into the view by dragging it over the data area to create a Measure Names/Values crosstab. Clicked on the pill to edit the table calculation to set the compute using to Advanced... on X and Y (partitioning on Group).
                    7. Validated that the Compute Using was accurate by scrolling down to the bottom of each Group to see the subtotals for SUM(Number of Records), that should match the # of Records in Group calc.
                    8. Created a "Total # of Records" calc with the formula TOTAL(SUM(Number of Records)). Brought that into the view by dragging it over the data area to create a Measure Names/Values crosstab. Clicked on the pill to edit the table calculation to set the compute using to Advanced... on Group, X, and Y. Though this doesn't change the displayed results, it does change how the calculation is computed from using a Table (Down) to using the fixed addressing of the specific dimensions. That way the table calculation results won't change when we move the pills around to build the view.
                    9. Validated that the Compute Using was accurate by scrolling down to the bottom of the crosstab to see the total for SUM(Number of Records), that should match the Total # of Records calc.
                    10. Created a "% of Records in Group" calc with the formula [# of Records in Group]/[Total # of Records], set the field to have a default format of %, then brought that into the view. Visually validated that the % was accurate.

                     

                    Now there's a working crosstab, we can create the view:

                     

                    1. Duplicate the workout worksheet.
                    2. Normally I'll turn off worksheet updates using the toolbar button at this point, because as I'm moving pills around Tableau will refresh the view in possibly-interesting but unecessary ways.
                    3. Move the Group pill to the Level of Detail Shelf.
                    4. Move X to Columns and make it Continuous.
                    5. Make Y continuous.
                    6. Put the % of Records in Group measure on Color.
                    7. Drag the other two measures to the Level of Detail.

                     

                    2013-11-26 11_07_24-Tableau - ExampleWorkbook.png

                     

                    Hope this helps!

                     

                    Jonathan