# Thread: Using data from two tables with different granularity Thanks:  1 Post #5183673 (1) Likes:  1 Post #5183673 (1)

1. ## Using data from two tables with different granularity

Hi,
I have two data tables. One has actual sales by Customer and Product code (Sales_By_Customer). The second (Sales Forecast), contains a total sales forecast by Customer only. I'm trying to calculate and chart the variance to target for each customer. I've created this measure:

Target v actual £s = sum(Sales_By_Customer[Total Sales Value £s])-sum('Sales Forecast'[Sales Forecast])

This works when I use a Card visualisation to display the total variance, but when I try and create a bar chart to show variance by individual customer the chart shows complete rubbish!.

I think the issue is probably caused by using data at different levels of granularity.

Does anybody have any ideas/pointers that may help me solve this issue please?

Thanks

Pete

2. ## Re: Using data from two tables with different granularity

Hi Charlklands

I cannot be of help without seeing your data. If you post some sample data then I can take look otherwise you could take a look at: https://www.daxpatterns.com/handling...granularities/

3. ## Re: Using data from two tables with different granularity

Hi,
Example of the data in each table:

Table 1
 Sales Forecast Customer Month Sales Forecast Customer X 01/11/2018 £728,945 Customer Y 01/11/2018 £271,203 Customer Z 01/11/2018 £165,207 Customer AA 01/11/2018 £96,732 Customer AB 01/11/2018 £41,923

Table 2
 Sales_By_Customer Invoice_Date Invoice Number Product code Description Customer Name Total Sales Value £s 01/11/2018 43294 7227 Product A Customer X £20,000 01/11/2018 42785 7228 Product B Customer Y £15,000 01/11/2018 46064 71120 Product C Customer Z £13,000 01/11/2018 43578 7771 Product D Customer AA £25,000 02/11/2018 40749 7227 Product A Customer X £11,000 02/11/2018 41971 7771 Product D Customer Y £45,000 02/11/2018 44833 7286 Product E Customer Z £20,000 02/11/2018 43701 7286 Product E Customer X £15,000

Hope this helps!

Pete

4. ## Re: Using data from two tables with different granularity

You need to create a Customer dimension which filters both tables. Then you can slice easily by Customer.

In the link I pasted there're also techniques which explain how not to display the forecast if you're slicing data by something else which is not a customer, like product in your example.

5. ## Re: Using data from two tables with different granularity

Originally Posted by VBA Geek
You need to create a Customer dimension which filters both tables. Then you can slice easily by Customer.

In the link I pasted there're also techniques which explain how not to display the forecast if you're slicing data by something else which is not a customer, like product in your example.

Thanks for your help, it all works now.

Thanks again!

Pete