Forum Discussion

dino19547's avatar
dino19547
Regular Visitor
4 years ago
Solved

Dividing by Non Zero Values Only

Hi PBI Community,

 

The below formula is set up to calculate price - dividing sales by quantities sold.

 

The fact that some lines show $0 values, dilute the true average unit price from $6.14 to $5.09 (because it divides by the zero values where these should be ignored.

 

How could I make the formula ignore $0 values?

 Below I show 

 

1 - formula

2- extract showing the detail of transactions showing the $0 values

3 - how it shows in the actual PBI report as $5.09 with the price being diluted

 

Thanks,

Price = CALCULATE(
DIVIDE(Sales[Sales Amount],[Qty Sold]))
 
 
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi dino19547 ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below to get the price:

    Price =
VAR _amount =
    CALCULATE (
        SUM ( 'Sales'[Sales Amount] ),
        FILTER ( 'Sales', 'Sales'[Sales Amount] <> 0 )
    )
VAR _qty =
    CALCULATE (
        SUM ( 'Sales'[Qty Sold] ),
        FILTER ( 'Sales', 'Sales'[Sales Amount] <> 0 )
    )
RETURN
    DIVIDE ( _amount, _qty )

    Best Regards

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    In the filter argument of CALCULATE, put condition as invoice <> 0

     

    Price =
    CALCULATE ( DIVIDE ( Sales[Sales Amount], [Qty Sold] ), invoice <> 0 )

     

    If you found this helpful, please mark this as a solution. Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi dino19547 ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below to get the price:

    Price =
VAR _amount =
    CALCULATE (
        SUM ( 'Sales'[Sales Amount] ),
        FILTER ( 'Sales', 'Sales'[Sales Amount] <> 0 )
    )
VAR _qty =
    CALCULATE (
        SUM ( 'Sales'[Qty Sold] ),
        FILTER ( 'Sales', 'Sales'[Sales Amount] <> 0 )
    )
RETURN
    DIVIDE ( _amount, _qty )

    Best Regards