6 Replies Latest reply on Aug 17, 2016 3:30 PM by Jess Lee

Calculation from 2 diff data sources with filter

Hi,

I have 2 data sources

source 1:

 Group Item participate Total A 1 10 50 A 2 20 20 A 3 3 60 A 4 1 20 B 1 5 5 B 2 6 10 B 3 10 100

source 2:

 Group Item yes_no A 1 Yes A 2 No A 3 No A 4 No B 1 No B 2 Yes B 3 Yes

Here is the result I'm looking for

 Group participate Total A 10 150 B 16 110

Is there anyway that i can calculate "participate" by filtering Yes only but for total will be both Yes and No answer?

thanks,

• 1. Re: Calculation from 2 diff data sources with filter

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

• 2. Re: Calculation from 2 diff data sources with filter

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.

• 3. Re: Calculation from 2 diff data sources with filter

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.

• 4. Re: Calculation from 2 diff data sources with filter

oh i want all numbers added up for total but for participate i only want to add where it is yes

Thanks

• 5. Re: Calculation from 2 diff data sources with filter

I just realized what you need.

See attached.

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.

1 of 1 people found this helpful
• 6. Re: Calculation from 2 diff data sources with filter

this is exactly what i need.  Thanks so much for your help