2 Replies Latest reply on Aug 22, 2016 7:25 AM by jessica.muse.0

    Percentage of Set within Top N% of Subset


      Basic Connection Details: Tableau 9.3 (yay, corporate upgraded me), connecting to Oracle database.


      Today's head scratcher, I'm looking for the Percentage of Overall Records that fall within the Top N (in this case 20) percent of a particular subset.  Due to the restrictions on my data, I've hacked together a similar situation from the Superstore Sales data (I think it was the Global Version) and have attached the .twbx.


      To summarize the request: I'm looking at defect data within the divisions of my company.  Defects have a text field, which we call barcode, that either have used a barcode (and meet a specified format) or do not.  To determine the "effectiveness" of the barcode, we'd like to see the percentage of overall defects (for that division, during that time period, etc.) that are accounted for in the top 20% of barcodes used for that section.  Using the Superstore sales data, I've pulled in Country, State, and City to mimic the divisions internally, the Order date (to represent the period of time, though it's not in the view in my dataset, just a parameter filter), the Order ID (to represent the barcode text), a calculated field for correctly coded (in this case I used regexp_match([Order ID],"$[US]-[0-9]{4}-[0-9]{6}$") to determine the yes/no status), and the number of units (which was just a count of Order ID).  Now, to further muck this up, I need to be able to roll up/down the data based on the locational hierarchy (so it all needs to calculate properly at the Country, State, and City level), and the result can only be the percentage for the location (and not all the records with it).  Using the data in the snapshot below, I'd like the result of Argentina - Buenos Aires - Buenos Aires: 35.48%.


      Working this, I know it will take several table calculations, and I can get a detailed report, but not the high-level summary with just the location and the percent.  Any help would be truly appreciated.


      I've mocked up a calculation here, attached as an image.