Using data from two tables with different granularity

[Chalklands]

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

 

[VBA Geek]

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-different-granularities/

 

[Chalklands]

Hi,
Example of the data in each table:

Table 1
[TABLE="width: 370"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Sales Forecast[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer[/TD]
[TD]Month[/TD]
[TD]Sales Forecast[/TD]
[/TR]
[TR]
[TD]Customer X[/TD]
[TD="align: right"]01/11/2018[/TD]
[TD="align: right"]£728,945[/TD]
[/TR]
[TR]
[TD]Customer Y[/TD]
[TD="align: right"]01/11/2018[/TD]
[TD="align: right"]£271,203[/TD]
[/TR]
[TR]
[TD]Customer Z[/TD]
[TD="align: right"]01/11/2018[/TD]
[TD="align: right"]£165,207[/TD]
[/TR]
[TR]
[TD]Customer AA[/TD]
[TD="align: right"]01/11/2018[/TD]
[TD="align: right"]£96,732[/TD]
[/TR]
[TR]
[TD]Customer AB[/TD]
[TD="align: right"]01/11/2018[/TD]
[TD="align: right"]£41,923[/TD]
[/TR]
</tbody>[/TABLE]

Table 2
[TABLE="width: 620"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Sales_By_Customer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Invoice_Date[/TD]
[TD]Invoice Number[/TD]
[TD]Product code[/TD]
[TD]Description[/TD]
[TD]Customer Name[/TD]
[TD]Total Sales Value £s[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2018[/TD]
[TD="align: right"]43294[/TD]
[TD="align: right"]7227[/TD]
[TD]Product A[/TD]
[TD]Customer X[/TD]
[TD="align: right"]£20,000[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2018[/TD]
[TD="align: right"]42785[/TD]
[TD="align: right"]7228[/TD]
[TD]Product B[/TD]
[TD]Customer Y[/TD]
[TD="align: right"]£15,000[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2018[/TD]
[TD="align: right"]46064[/TD]
[TD="align: right"]71120[/TD]
[TD]Product C[/TD]
[TD]Customer Z[/TD]
[TD="align: right"]£13,000[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2018[/TD]
[TD="align: right"]43578[/TD]
[TD="align: right"]7771[/TD]
[TD]Product D[/TD]
[TD]Customer AA[/TD]
[TD="align: right"]£25,000[/TD]
[/TR]
[TR]
[TD="align: right"]02/11/2018[/TD]
[TD="align: right"]40749[/TD]
[TD="align: right"]7227[/TD]
[TD]Product A[/TD]
[TD]Customer X[/TD]
[TD="align: right"]£11,000[/TD]
[/TR]
[TR]
[TD="align: right"]02/11/2018[/TD]
[TD="align: right"]41971[/TD]
[TD="align: right"]7771[/TD]
[TD]Product D[/TD]
[TD]Customer Y[/TD]
[TD="align: right"]£45,000[/TD]
[/TR]
[TR]
[TD="align: right"]02/11/2018[/TD]
[TD="align: right"]44833[/TD]
[TD="align: right"]7286[/TD]
[TD]Product E[/TD]
[TD]Customer Z[/TD]
[TD="align: right"]£20,000[/TD]
[/TR]
[TR]
[TD="align: right"]02/11/2018[/TD]
[TD="align: right"]43701[/TD]
[TD="align: right"]7286[/TD]
[TD]Product E[/TD]
[TD]Customer X[/TD]
[TD="align: right"]£15,000[/TD]
[/TR]
</tbody>[/TABLE]


Hope this helps!

Pete

 

[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.

 

[Chalklands]

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