Skip to main content
cancel
Showing results for 
Search instead 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

Reply
nedpbi
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.

 

nedpbi_0-1719349038954.pngnedpbi_1-1719349061000.pngnedpbi_2-1719349082418.png

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 ?

nedpbi_3-1719349200591.png

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
v-jialongy-msft
Community Support
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:

vjialongymsft_0-1719365785713.png

 

 

 

 

Best Regards,

Jayleny

 

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

View solution in original post

3 REPLIES 3
nedpbi
Frequent Visitor

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

 

nedpbi_0-1719436680709.png

 

 

v-jialongy-msft
Community Support
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:

vjialongymsft_0-1719365785713.png

 

 

 

 

Best Regards,

Jayleny

 

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

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

 

nedpbi_0-1719408947263.png

 

Thank you !

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.