Have you tried SUM(Number of Records)/(Unique Provider Numbers)
If Unique Provider Numbers is already aggregated, no need to wrap it in another aggregation.
Thanks. I did try that one, and it gave me this error: "The formula must be a valid table calculation."
So I assume that "Unique Provider Numbers" is a table calculation? Can you post the workbook, or a sample workbook with the same kind of setup? If not, let us know what that calculation for Unique Provider Numbers is.
"Unique Provider Numbers" is a calculated field I created to return me the unique values of another field called "Provider Numbers." The calculation was just this: COUNTD([provnum] )
Basically I have three fields for my text table viz: "States", "Number of Records" and my calculated field called "Unique Provnum".
States = U.S. states
Number of records = Number of violations
Provider numbers = Nursing homes
Unique provum = Nursing homes (avoiding duplicates that appear since some nursing homes have multiple violations)
I dragged States, Records and Unique into the viz and tried to do a customized table calc. That's where I'm stuck.
How do I post a workbook? I'm still new to this.
OK. I don't believe you need this to be a Table Calculation. Try just creating this as a regular calculated field.
To attach a workbook, go to "Use Advanced Editor" in the top right corner of your message box--then, you'll see "Attach" at the bottom right hand side of this window.
OMG, you're a genius! I just created a calculated field and it worked beautifully!
How do you know when to do a calculated field and when to do a table calculation? What's the difference?
I'm really not, but thanks anyway . I've only been doing this myself for 5 months or so, but there's a whole lot of material to learn. Per the link below, Tableau defines a Table Calculation as (I don't have my own definition, but typically I use a Table Calculation to calculate something I cannot get any other way!)
Table calculations are computations that are applied to the values in the entire table and are often dependent on the table structure itself.
Some basic examples of Table Calcuations include: Percent of total, ranking calcs, moving averages, year over year comparisons, etc.
Some good info on learning Table Calcs here: Want to Learn Table Calculations? Here’s How! | Drawing with Numbers
Thanks so much. (You're actually really good at this considering you've only been doing it five months.)