4 Replies Latest reply on Oct 19, 2012 2:20 PM by Ken Zheng

# How to join two fact table with a common dimension at different granularity

Hi

In tableau, how could I join two fact table with a common dimensional table at different granularity to create a view.

I have two fact tables:

1) daily sales:  sale # at daily level

2) Monthly target: target # for each month.

I have one dimensional table, dDate, which is linked to the two fact tables at different granularity.

dDate has one date per row in the table, the fields in dDate:

DayDate, WeekDate, Month, Year ....

The daily sales is mapped to DayDate field, and monthly target is mapped to Month field.

When I join the 3 tables together in Tableau to create a view, comparing monthly sale and monthly target, The aggregation of target # is counted at daily level instead of monthly level. That completely screwed up the number.

What do I miss here?

How to create a view to compare the aggregated daily sales to month level with monthly target?

Thank you

• ###### 1. Re: How to join two fact table with a common dimension at different granularity

Hi,

Depending on how you are setting up your view, you might be able to use an aggregate like MIN() or ATTR() on the monthly target when SUM(Daily Sales) gets you that total.

If that doesn't work, then you may need to use a table calculation to accomplish your aggregation, in that case posting a packaged workbook (.twbx) with some sample data would be good.

Jonathan

1 of 1 people found this helpful
• ###### 2. Re: How to join two fact table with a common dimension at different granularity

Hi Jonathan,

Thank you for your prompt answer. The MIN() and ATTR() are helpful at the first place, however, it is not working once I try to do some aggregate on those monthly target.

For example, the target # is stored at per product per month, and I could use ATTR() or MIN() to get the correct number displayed at a month/product pivot table. However, when I try to get the total target number for each month, across products, the function didn't give me the number I want.

How to do monthly target number in this case ?

Thank you

• ###### 3. Re: How to join two fact table with a common dimension at different granularity

Hey Ken -

Another approach you could take is to simply NOT join all three tables together. Instead:

• Join your date dimension and daily sales fact table together in one data source
• Bring in your monthly goal fact table in as a second, distinct data source
• Use Data Blending to marry the two together.

I've attached a sample, including the 3 tables (in an Excel worksheet) that I created to drive the sample....

• ###### 4. Re: How to join two fact table with a common dimension at different granularity

Chris,

That is AWESOME. It works and the key is to set the level of details to Monthly.

BTW,

I want to make the same mark/bar chart like what you did in you sample, but ended up with this below. How to get to that chart from here.

Thank you