AVERAGEX formula help

[kekepania0529]

I have the following excel formula that I need to convert to DAX for Power BI (Power BI column names are in italics within the excel formula):

Area Sell Contract Price = IFERROR(SUMIFS('Sell Contract Details'!$Z:$Z Sell Extended Price, 'Sell Contract Details'!$D:$D Area, 'Inbound Details'!$H14 Area, 'Sell Contract Details'!$L:$L Product Class, 'Inbound Details'!$B14 Material Grade) / SUMIFS('Sell Contract Details'!$Y:$Y Sell Order LB, 'Sell Contract Details'!$D:$D Area, 'Inbound Details'!$H14 Area, 'Sell Contract Details'!$L:$L Product Class, 'Inbound Details'!$B14 Material Grade), "")

Sell Contract Details and Inbound Details are unrelated tables in my Power BI report. I cannot create a relationship because neither table has a unique identifier (many:many). The join is on Inbound Details.Area = Sell Contract Details.Area and Inbound Details.Material Grade = Sell Contract Details.Product Class.

I was able to get the Average Sell Contract Price with the following DAX formula:

Area Sell Contract Pricing = AVERAGEX(FILTER('Sell Contract Detail','Sell Contract Detail'[Area] = 'Inbound Detail'[Area] && 'Sell Contract Detail'[Product Class Desc] = 'Inbound Detail'[Material Grade]), DIVIDE('Sell Contract Detail'[Sell Order Extended Price 2],'Sell Contract Detail'[Sell Ordered LB 3],0))

...but if there is more than 1 'Inbound Details'[Loads] per 'Inbound Detail'[Supplier], the result is the 'Sell Contract Detail'[Average Sell Price] * 'Inbound Detail'[Loads].

I tried DIVIDE('Sell Contract Detail'[Average Sell Price],'Inbound Detail'[Loads],0)), but the result is the same.

Any suggestions?

[MFelix]

Hi @kekepania0529 ,

Without data is difficult to give any correct way of formulating the measure.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.