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

    Calculation from 2 diff data sources with filter

    Jess Lee

      Hi,

       

      I have 2 data sources

       

      source 1:   

         

      GroupItemparticipateTotal
      A11050
      A22020
      A3360
      A4120
      B155
      B2610
      B310

      100

       

      source 2:

         

      GroupItemyes_no
      A1Yes
      A2No
      A3No
      A4No
      B1No
      B2Yes
      B3Yes

       

      Here is the result I'm looking for

         

      GroupparticipateTotal
      A10150
      B16110

       

      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
          Joe Oppelt

          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
            Jess Lee

            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
              Joe Oppelt

              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
                Jess Lee

                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
                  Joe Oppelt

                  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
                    Jess Lee

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