In your sample data, for example, we know from primary table that GroupID 12 had 100 inspections. From secondary table we know that GroupID 12 inspections had some Bruise and some Color defects, but none of the Rust. How would we figure out the exact number of Bruise and the exact number of Color? It can be 1 and 99, or 50 and 50 or any other combination adding up to a 100, and the data will still look like your example.
Is there something missing from the data?
There were N cases in each groupId - and the defect(s) found apply to all N of them.
So in GroupID 11 and 12 there were 150 cases in total, and 100 had bruising.
"So in GroupID 11 and 12 there were 150 cases in total, and 100 had bruising."
And the same 100 also had color defect? (according to secondary table)
If there were 100 cases in that GroupID - then whatever defects were identified apply to the entire Case Count.
Then I would recommend pivoting your secondary table so that it has this structure:
GroupID Has Bruise Has Color Has Rust 12 Yes Yes No 14 Yes Yes Yes 15 Yes No No
This will avoid duplication of counts and you can use calculations to do the ratios, e.g. for ratio of Bruise defects:
SUM(IIF([Has Bruise] == 'Yes',[Cases Inspected],0))/SUM(Cases Inspected])
The pivoting can be done via SQL on the fly, or using Tableau add-in for Excel, or transposing in Excel, or via any other method.
Thanks Dimitri. I'm trying hard to solve within the workbook entirely, and not pivot elsewhere.
We have figured out a solution:
- Create a duplicate of the joined data-source
- Remove the joined table (effectively creating a flattened table)
- Write a table calc that uses SUM([Qty of Cases - joined table]) / TOTAL(SUM(Qty of Cases - flattened table))
- Ensure the table calc correctly calculates at CELL LEVEL.
It's not pretty - but it works!