topic Re: Show overall average price per product but only for product with sales in DAX Commands and Tips

Show overall average price per product but only for product with sales

nedpbi — Tue, 25 Jun 2024 21:11:51 GMT

Dear Community Members,

I have a product table and a sales table like the below. Both are linked by product.

I have two measures average price and overall average price which is the total sold amt / total qty.

When I display them in the table they come up correctly but as below. Is it possible to define the overall overage price so that it is only calculated for products that have sales against them ?

I have attached the sample pbix file.

https://www.dropbox.com/scl/fi/rp5x90ddjl6we3wzr53u1/Overall-Avg-Price.pbix?rlkey=x2hfc8x71obqqwpa5qxdhey6k&st=we9tg7oe&dl=0

Thank you for any suggestions !

Re: Show overall average price per product but only for product with sales

Anonymous — Wed, 26 Jun 2024 01:36:32 GMT

Hi nedpbi

You want only calculated for products that have sales, you just need to add a filter with sales greater than 0.

DAX:

Overall Avg Price for Products with Sales = CALCULATE( DIVIDE(SUM(Sales[Amt]), SUM(Sales[Qty])), FILTER( Sales, Sales[Qty] > 0 ) )

Result:

Best Regards,

Jayleny

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

Re: Show overall average price per product but only for product with sales

nedpbi — Wed, 26 Jun 2024 13:37:06 GMT

Thank you Anonymous for your reply !

When I try the formula above I get the below. I want to see 2.17 repeated along all the rows for all articles (or attribute of article like brand in this case) but only show rows that actually have sales rather than all articles from the master

Thank you !

Re: Show overall average price per product but only for product with sales

nedpbi — Wed, 26 Jun 2024 21:18:23 GMT

Anonymousmaybe did not understand correctly, but tried the below and this seems to work. Thanks again for the help !