cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

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

View solution in original post

2 REPLIES 2
Highlighted
Super User III
Super User III

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Community Champion
Community Champion

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors