Tushar, thanks for the response!
Couple of things. The actual workbook I am doing this on is very large, and the cross join is slow. Data blending performs significantly faster. Do you by change know how to replicate your above approach using blending rather than a join?
Also, I cannot open your 10.3 workbook since I am still on 10.1 for another week. How did you get the pie chart set up to work like that?
Stephen, I understood your concern. However, I cannot use LOD while blending. So, you don't get the desired result.
This is what I got using blending.Not even closer to your actual requirement.
I am not sure how to do this now using blending. So, I've used cross-data source filtering. Another approach would be, combine these two tables using custom SQL and perform the required analysis.
Now, coming to my solution. Just create the calculated field mentioned in the above post and you will get the pie chart.
Hope this makes some sense.