Forum Discussion

kekepania0529's avatar
kekepania0529
Icon for Helper I rankHelper I
4 years ago

AVERAGEX formula help

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?

Need Help

1 Reply

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    4 years ago

    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.