2 of 2 people found this helpful
It looks like it's because of different filters being used between the two sheets. I took all the filters off both sheets and got the same value (7.93) for PowerCenter.
You're using an 'INCLUDE' LOD calculation - which means it is being calculated after the dimensions on your filters shelf. See Filters and Level of Detail Expressions
Hope that helps.
P.S. It's also worth bearing in mind that the datasources are technically being blended rather than joined - which will lead to slightly different behaviour when it comes to using LODs. This thread has a bit more info on that: how to aggregate blended measures?
Thanks David. Yes you are right, both the sheets have different set of filters(because of multiple data source) but unfortunately this is the requirement i have.
I did scrum through various threads but could not find any similar issue. Thanks Anyways.
1 of 1 people found this helpful
FYI I prefer Jonathan to shortening my name to Jon, thx!
First of all going back to what David wrote the two sheets are apples and oranges, besides being from different data sources (with different Prod Line Groups) there are filters on one that are not on the other. So to get the same results in both sheets the underlying data feeding into the calculations MUST have the same filtering in place.
Now the Sheet 2 is built using a data blend. Tableau data blends are not a join exactly, that's an oversimplification. A more useful simplification is that data blending is a kind of post-aggregate join and that imposes limits on what kinds of calculations and filtering we can do. So, for example, If I bring in a dimension filter from APAC... source into Sheet 2 to filter for the same Ps Titles used in Sheet 1 then the Buffer calculation breaks because some of the sub-calcs are using COUNTD() and COUNTD() is one of the functions that has more limited use when using data blending. I've done some presentations on this in the past, if you want I can look them up.
So to get the desired results you're going to need to do some data prep in order to have Sheet 2 have the same results. Given the complexity of the calculations I'm strongly leaning against using a data blend, my suggestion would be to figure out a way you can do some sort of join (potentially an outer join) or union on the tables and then build measures from there, potentially using LOD expressions to deal with replicated rows that are likely to result.
Makes sense, will try to figure this out. Thanks Jonathan for your quick response.