cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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 !

1 ACCEPTED SOLUTION
Community Support

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.

3 REPLIES 3
Frequent Visitor

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

Community Support

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.

Frequent Visitor

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 !

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.