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

ProductMnthlyAvgPrice
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

DateProductRevenue
1/1/2018Product A271.7
2/1/2018Product A98.8
3/1/2018Product A135.15
1/1/2018Product B9.18
2/1/2018Product B125.96
3/1/2018Product B48.45
1/1/2018Product C5.96
2/1/2018Product C17.55
3/1/2018Product C23.69


VolumeTable

DateProductFC?Qty
1/1/2018Product AActual95
2/1/2018Product AActual40
3/1/2018Product AActual53
4/1/2018Product AForecast72
5/1/2018Product AForecast73
6/1/2018Product AForecast65
7/1/2018Product AForecast48
1/1/2018Product BActual6
2/1/2018Product BActual47
3/1/2018Product BActual17
4/1/2018Product BForecast18
5/1/2018Product BForecast24
6/1/2018Product BForecast47
7/1/2018Product BForecast43
1/1/2018Product CActual2
2/1/2018Product CActual9
3/1/2018Product CActual23
4/1/2018Product CForecast15
5/1/2018Product CForecast88
6/1/2018Product CForecast44
7/1/2018Product CForecast92
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]
)

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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