Forum Discussion

rhildeb's avatar
rhildeb
Frequent Visitor
8 years ago
Solved

AVERAGEX from multiple tables

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

     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's avatar
MFelix
Super User
8 years ago

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