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

[nedpbi]

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=x2hfc8x71obqqwpa5q...

Thank you for any suggestions ! 

[nedpbi]

@v-jialongy-msftmaybe did not understand correctly, but tried the below and this seems to work. Thanks again for the help !

[v-jialongy-msft · Accepted solution]

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.

[nedpbi]

Thank you @v-jialongy-msft 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 !