Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rhildeb
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
Anonymous
Not applicable

 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
Anonymous
Not applicable

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

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



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.