Using data from two tables with different granularity

Chalklands

New Member
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

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

Chalklands

New Member
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

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Hope this helps!

Pete

VBA Geek

MrExcel MVP
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.

Chalklands

New Member
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