4 Replies Latest reply on Nov 24, 2016 3:53 AM by Marie-claire Fournier

# Filter & Calculations on blended data

Hello,

I've been struggling with this one for a few days now.

In summary :

I have 2 data sources containing the same data but being filtered for different periods.

Filters are applied at context level as I'm using LOD calculations.

Same KPI are calculated in both data sources. I'm struggling to calculate the difference between the KPIs of the 2 periods as they get affected by filters.

In illustrated details :

DetailsIllustration
Data source

Requirement :

End user can select the current period

Requirement :

KPI calculation for

- current period

- previous period

- difference between current and previous

This is where it all goes wrong

Data structure

(maybe this is not the best approach,

I'm open to suggestions)

- 2 extracts of the same data

- filtered by boolean : selection date

Problem : Filter All using related data

on blended data

Is there a way to have the criteria 1 filter

acting on both data source independently ?

Reason why date selection is a filter

set up at context level and why I use 2

data sources

This is because of the KPI :

% of Suppliers in 80% of Spend Previous Period

I'm documenting it, in case someone wants to

steal the idea.

Also, the next step will be to calculate the

difference between these 2 KPIs.

Calculations :

Worksheet :

table calculations to be done at vendor level

sort the vendor by descending order by "1 Spend by Suppliers"

I am attaching both excel data source and the packaged tableau file. Hopefully, my explanations are clear enough. I found it difficult to explain simply a complex problem.

I would be grateful if anyone could help me with this calculation issue.

Thanks very much

Marie-Claire

• ###### 1. Re: Filter & Calculations on blended data

Hi,

I think you do not need 2 data sources. Besides blending is dangerous...

Łukasz

• ###### 2. Re: Filter & Calculations on blended data

Hello Łukasz,

I understand that blending is dangerous and that for most of the KPI 2 data sources are not required.

How do you suggest I calculate the difference between the number of suppliers in the top 80% of spend ? These calculations are table calculations.

Thanks very much.

• ###### 3. Re: Filter & Calculations on blended data

Those  differences were not included in your wb so i did not implement them.

In this approach you need two sets of each calculation: current and previous and (nested) table calculations must be sorted accordingly:

1 of 1 people found this helpful
• ###### 4. Re: Filter & Calculations on blended data

The solution is custom sorting.

Thanks very much !! Your help is very much appreciated.

Marie-Claire