12 Replies Latest reply on May 24, 2017 10:05 AM by Holly Ftizgerald

# Calculations between 2 worksheets using the same data but different filters

I have 2 worksheets, each using their own set of multiple filters but both are using the same data. I do this so I can have 2 user-defined groups that they'd like to compare alongside one another. The 2 worksheets are identical with the same measures/filters except one set of filters are applied to one sheet and the other set to the other sheet. What I'm looking to do now is calculate the difference between those user-defined/filtered results.

I feel like this should be pretty simple but I'm having a tough time as the calculated field [Met Std. Calc] I created like this:

COUNT (IF([Met Std] = 1) THEN 1 END) / COUNT([Met Std])

is used in each sheet, so simply doing [Met Std. Calc] - [Met Std. Calc] wouldn't work. Is creating 2 versions of that like [Met Std. Calc A] and [Met Std. Calc B] and then subtracting those be the solution? Or, is this more complicated than I hope and would require a more involved/complicated solution?

Any help or direction you could push me towards would be greatly appreciated!

• ###### 1. Re: Calculations between 2 worksheets using the same data but different filters

You'll need to use LoD calcs with context filters.  Can you post up a sample dataset/packaged workbook and if need anonymize the data or recreate in Superstore.

• ###### 2. Re: Calculations between 2 worksheets using the same data but different filters

So here it is. Should be pretty self-explanatory once you see how I have it set up and should hopefully see what I'm looking to do (calculate the difference between A and B).

Let me know if there are problems with that file. Thanks!

• ###### 3. Re: Calculations between 2 worksheets using the same data but different filters

Did you have a chance to look at the workbook I posted? If not no worries, just checking.

If anyone else wants to take a look I'd love your help. Thanks!

• ###### 4. Re: Calculations between 2 worksheets using the same data but different filters

Bryan,

Filters and LoD is most likely the best way to go, but I was wondering if using parameters could work for your setup.

It has its drawbacks, but potentially could have some benefit too.

There would be two identical parameters of each type ( [EthnicityA], [EthnicityB]...) being string lists of the respective field, and then you would perform a separate calculation of A and B like:

[Met Std Calc A]:

AND [Test]=[TestA]

AND ...

THEN [Med Std Calc]

END

The drawback is that the parameter wouldn't dynamically change with new entries to each field.

Though it could most likely be done with filters too, I think the parameter method allows you

to put both line graphs (and the difference) on the same chart, if that would be useful.

1 of 1 people found this helpful
• ###### 5. Re: Calculations between 2 worksheets using the same data but different filters

This looks great! I'm going to have to dig in deeper and see if it might work for me with some adjustments and added parameters

I got help a little while ago using parameters as the solution on a similar request but the problem with the parameters (for me, at least) was in the handling of ALL values. For example, I have a field that checks for English as a 2nd language (a Y or N) and when the user selects YES or NO it properly displays just those students but when I choose the option for ALL (in other words, show both the Y and N values in the same series) it fails. I know adding the ALL functionality to parameters has been the top user suggested Tableau feature for quite a while and your solution (and the one someone else assisted me with) would work perfectly if they'd handle the ALL option properly, but thus far I'm just not exactly getting it to work. Do you have any experience with the solution you sent and including an option to display ALL values for that parameter?

Thanks again for the help!

• ###### 6. Re: Calculations between 2 worksheets using the same data but different filters

Bryan,

My apologies for not paying attention to the Alls.

I think it can still work by just adding "All" to the parameters and then

adding All to the calculation with enclosing parentheses:

IF ([ContainerAall]="All" OR [Container]=[ContainerAall])

AND ([CustomerSegmentAall]="All" OR [Customer Segment]=[CustomerSegmentAall])

...

THEN [Sales] END

Maybe Nulls can be handled similarly.

• ###### 7. Re: Calculations between 2 worksheets using the same data but different filters

Wow, this looks like it's going to work...no clue why I was having such a hard time before with ALL values, it just never really translated easily I guess. I'm implementing the logic you have in here into my own workbook and will report back with my progress, perhaps post a copy of my workbook if I'm getting tripped up. But, the way you handle the ALL values here is very clean and deceptively simple; if I can replicate it with several parameters and get it to accurately calculate the numerator and denominator in my "met standard" calculation I should be all set. Well done and thanks again!

• ###### 8. Re: Calculations between 2 worksheets using the same data but different filters

Check out the attached workbook. I used the logic from your example on my data and I'm running into the same problem I've run into before: the all option displays a series for each value. So in my example, when you select ALL for the ELL Param A you now see a series for Y and another for N, it's not showing it as a single series containing all values. I'm guessing the problem is in how I calculate the met std:

IF ([ELL Param A]="All" OR attr([Bilingual ESL])=[ELL Param A])

AND ([Gender Param A]="All" OR attr([Gender])=[Gender Param A])

THEN COUNT (IF([Met Std] = 1) THEN 1 END) / COUNT([Met Std]) END

In the data, met std is a 1 if they met std (what I'm trying to figure out) or a 0 if they did not and so I do a count for the numerator. In your example I see that you just use a SUM function on sales in your example...could I perhaps alter this so I do a sum on met std. and then use the # of records to determine the denominator? Would that change anything? My application is always tough to relate to superstore since I need to aggregate/count and such rather than just summing up the sales and so I struggle with the great help here because solutions always require me to view it all a bit differently (oh how I wish I was just summing up sales!). Thanks for your patience.

1 of 1 people found this helpful
• ###### 9. Re: Calculations between 2 worksheets using the same data but different filters

Bryan,

I'll need to play around with it more, but I do agree with you that there might be

promise in removing the aggregation from the calculation.

I don't think you need to even sum it in the calc:

IF ([ELL Param A]="All" OR [Bilingual ESL]=[ELL Param A])

AND ([Gender Param A]="All" OR [Gender]=[Gender Param A])

THEN [Met Std]

END

And then afterwards just sum the Calc and divide it by the count?

Will look into it some more.

1 of 1 people found this helpful
• ###### 10. Re: Calculations between 2 worksheets using the same data but different filters

Success! See the attached workbook. I looked at the one you sent with the ALL values for the differences between the 2 and noticed you had wrapped the items on the marks shelf in attr() so I did that and then ditched the calculation in the formula so it now looks like this:

IF ([ELL Param A]="All" OR [Bilingual ESL]=[ELL Param A])

AND ([Gender Param A]="All" OR [Gender]=[Gender Param A])

THEN [Met Std] END

and I changed the measure value like this:

[ALL Calc A] / [Number of Records]

and all of a sudden SUCCESS! I'm not exactly sure why wrapping the marks on the shelf in attr() fixed everything but there it is. This is EXACTLY what I was looking to do and I can now start adding more parameters to deal with the rest of the dimensions I'd like to filter on as well as some user-defined filters that I'd like to be applied to both the "compare" series. Good stuff.

Thanks a ton for the help!

1 of 1 people found this helpful
• ###### 11. Re: Calculations between 2 worksheets using the same data but different filters

Bryan,

Glad you were able to sort it through.

Nice work!

• ###### 12. Re: Calculations between 2 worksheets using the same data but different filters

This solution is exactly what I needed to do! I cannot thank both of you enough.

I have been working for while to allow a user defined (filtered) subset of a population to be compared and used in calculations with the total population.