AVERAGEX from multiple tables

[rhildeb]

I'm attempting to calculate the MnthlyAvgPrice as a Column. There are 3 tables and they are linked through the "Product" field. I know how to do this adding columns, but I'm trying to figure out how to do it without adding additional columns.

I know the following formula isn't really even in DAX format, but I've written it this way to hopefully make it clear what I'm trying to do.

      MnthlyAvgPrice = Averagex( Filter=product, ActualsTable[Revenue] / VolumeTable[Qty])

 Thanks!

ProductTable

Product	MnthlyAvgPrice
Product A	$2.63
Product B	$2.35
Product C	$1.99

Values in red were manually calculated, but this is what I want to the formula to calculate 

ActualsTable

Date	Product	Revenue
1/1/2018	Product A	271.7
2/1/2018	Product A	98.8
3/1/2018	Product A	135.15
1/1/2018	Product B	9.18
2/1/2018	Product B	125.96
3/1/2018	Product B	48.45
1/1/2018	Product C	5.96
2/1/2018	Product C	17.55
3/1/2018	Product C	23.69

VolumeTable

Date	Product	FC?	Qty
1/1/2018	Product A	Actual	95
2/1/2018	Product A	Actual	40
3/1/2018	Product A	Actual	53
4/1/2018	Product A	Forecast	72
5/1/2018	Product A	Forecast	73
6/1/2018	Product A	Forecast	65
7/1/2018	Product A	Forecast	48
1/1/2018	Product B	Actual	6
2/1/2018	Product B	Actual	47
3/1/2018	Product B	Actual	17
4/1/2018	Product B	Forecast	18
5/1/2018	Product B	Forecast	24
6/1/2018	Product B	Forecast	47
7/1/2018	Product B	Forecast	43
1/1/2018	Product C	Actual	2
2/1/2018	Product C	Actual	9
3/1/2018	Product C	Actual	23
4/1/2018	Product C	Forecast	15
5/1/2018	Product C	Forecast	88
6/1/2018	Product C	Forecast	44
7/1/2018	Product C	Forecast	92

[Anonymous · Accepted solution]

 In order to achieve this, we will need a Date Table and you will need to have this linked to both of your Actuals and Volume tables.  Your date table will need a "YearMonth" style column to help us detiremine what a single month is.

First, you want to create a measure first that does the base job you seek, which is to get an average price regardless of context.  Something like

Price = DIVIDE(
	Sum(ActualsTable[Revenue]), 
	Sum(VolumeTable[Qty])
)

From here, you will want to run this particular measure for each month and get an average of those results, run under the context of each product. This can be done as a measure (best practice) or as a custom column should you have that particular need.  The columns code would be:

MnthlyAvgPrice = AVERAGEX(
	values('Dim - Date Table'[YearMonth]), 
	[Price]
)

[MFelix]

Hi @rhildeb,

Add a calendar table and relate it to the other two table then add the following measure:

Average =
CALCULATE (
    DIVIDE ( SUM ( ActualsTable[Revenue] ); SUM ( VolumeTable[Qty] ) );
    ActualsTable
)

On your visual place date from Calendar, Product from products and the measure.

Regards,

MFelix