Sure. It should add up all [Total] for any [Group] that has at least one "Yes" in the secondary source.
What will bite you is if you have some [Group] that doesn't have any "Yes" rows in the secondary source.
See attached. I added a C group to the primary.
Example is 9.0
Participate.twbx 13.6 KB
hmmm if you look at group A, there is only 1 yes which should be 10 and group B has 2 yes = 16. However, the filter doesn't seem to work. It still adds up all numbers.
I thought you wanted all the numbers added up though. That's how I get a total of 150 for "A".
For the record, I didn't blend on [ITEM]. That's how I get it to add up all the "A" values even though there Is "NO" in the secondary rows.
oh i want all numbers added up for total but for participate i only want to add where it is yes
I just realized what you need.
You DO need to blend on Item as well. I missed that part.
I made some changes. I added ITEM to the blend, and added it to the sheet. (I also dragged [YesNo] to the primary data source via a calc, but I think that wasn't really necessary.) So on Sheet 1 the values are there, but broken out by ITEM as well.
Then I did Table calcs to sum up the two measures. For [Total] I just add up all Total. For [Participate] I have an extra bit of logic only to add if the row is YES. Added those to the sheet, and edited the table calc to run along [ITEM].
That gives you the numbers you want, but not the viz you want. See Sheet 2.
Here I moved [Item] and [Yes/No] to the data shelf so that the calcs still have the dimension to run along.
And I added one more thing. I created a calc called INDEX and added it to the filter shelf. I have that running along [Item] as well. And I have the filter just grab the first row for each [Group]. (Pull it off the filter shelf to see what happens without it. We need [ITEM] on the sheet to make the calc work, but with [ITEM] in there, we get multiple copies of the table calc, one per row for each [ITEM]. So just grab the first occurrence.)
We cannot do an actual quick filter to chop out YES or NO. If we do, the actual rows are gone, so we can't get the full total of [Total]. But with table calcs we can have all the data on the sheet's table, but just show what we want this way.
Participate 2.twbx 21.4 KB
this is exactly what i need. Thanks so much for your help