topic Need Average DAX for Part Sales but data in table is from Part Inventory table in DAX Commands and Tips

Need Average DAX for Part Sales but data in table is from Part Inventory table

victoria_g06 — Tue, 11 Jun 2024 05:33:29 GMT

Hi All,

I'm sure this is quite a simple issue but I am not getting the results I want.

I have this table for Parts Inventory data, (one shown for example) and I would like to show the average sales (monthly?)

When I add the average dax it unfilters my table and adds three lines per part (The other lines are for other branches)

AverageSales =

AVERAGEX(PartSales_Parts, PartSales_Parts[SaleUnitAmount] * PartSales_Parts[ShippedQuantity])

Any help would be greatly apreciated!

Re: Need Average DAX for Part Sales but data in table is from Part Inventory table

lbendlin — Tue, 11 Jun 2024 16:21:25 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Need Average DAX for Part Sales but data in table is from Part Inventory table

victoria_g06 — Tue, 11 Jun 2024 22:16:33 GMT

Hi There, Sorry it wont let me post a html table of data.

Re: Need Average DAX for Part Sales but data in table is from Part Inventory table

lbendlin — Wed, 12 Jun 2024 00:37:02 GMT

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Re: Need Average DAX for Part Sales but data in table is from Part Inventory table

victoria_g06 — Wed, 12 Jun 2024 00:44:31 GMT

Yes I read that and it wont allow me to post the html table. Please disregard this post.

Re: Need Average DAX for Part Sales but data in table is from Part Inventory table

Anonymous — Wed, 12 Jun 2024 06:59:24 GMT

Hi victoria_g06 ,

First of all, many thanks to lbendlin for your very quick and effective replies.

Based on the description, please try the following dax formula.

AverageSales = CALCULATE( AVERAGEX( PartSales_Parts, PartSales_Parts[SaleUnitAmount] * PartSales_Parts[ShippedQuantity] ), ALLEXCEPT(PartSales_Parts, PartSales_Parts[PartID]) )

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Need Average DAX for Part Sales but data in table is from Part Inventory table

victoria_g06 — Wed, 12 Jun 2024 22:19:20 GMT

Thank you,

It works but I have an issue though becuase my data is from the PartsInventory Table, so it wont show the correct figures because the sales table is a different table.

I will mark is as the solution and keep working on the issue.

Thank you