The easiest approach depends on your data and how you want to display the analysis.
For example, you could use AVG(Population) if all of the rows in the partition you're aggregating over (Attribute, in this case) have the same value for Population. Then to calculate the % defective, you can use SUM(Defective Population) / AVG(Population). No table calcs required.
This fails when you need sum the blues for multiple products, where the population for each blue row may differ. For example, if you had a blue hood product/attribute with a population 10 and only one row, you'd want the total for blue to be 40, not the AVG(Population) which would be 70/3.
In this case you can use the table calc. approach you reference above:
Population per Attribute = IIF(FIRST()==0, MIN(Population), NULL))
calculated across a copy of the Attribute dimension (compute using Attribute (copy)).
To sum all of the blues for multiple products, you'll have to have Product on the level of Detail shelf and use another table calc to sum the Population per Attribute across the Product dimension.
Population per Attribute Total = IIF(FIRST()==0, WINDOW_SUM([Population per Attribute]), NULL))
with compute using set to Product.
A similar approach would be needed to sum the defective population across the Product dimension.
IIF(FIRST()==0, WINDOW_SUM(SUM([Defective Population])), NULL)
Also set to compute using Product. (Again this assumes Product is not in the view and you don't, therefore, have a separate set of marks for each product.)
example.twbx 34.8 KB
Thank you so much, your post and especially your example workbook are immensely helpful.
Sorry I missed this earlier in the week. With table calcs, the key is always compute using which determines have values are used in the table calculation. Population per Attribute Total is a nested table calc --- a WINDOW_SUM() of another table calc, which means you can set two different aggregation levels.
If you click this pill > Edit Table Calculation, you should see a pull-down menu for each field that allows you to set the Compute Using (aka addressing). Check that it looks like the below. If this doesn't fix the issue, could you post an updated workbook (or perhaps we could do a short screen share).
If this fixes the issue, but is still confusing, let me know and I'll try to provide a better explanation when I've got a few minutes.
Thanks for the answer, I ended up changing my data set to make everyones life easier.