If two conditions are met then use measure

[ewuchatka]

Hello

I have two columns with many products: 

Weight (g)

Price (£)

Product

Type

I'd like to calculate £/kg for a particular product and group which is easy  in principle = £/kg = sum('Power BI'[Price per Unit])/sum('Power BI'[Weight])*1000

However, there are occassions where:

- Weight is "unidentified" (simply, it is a text I want to ignore)

- Price is "zero" and I don't want to do a calculation in that case

If in totals (e.g. at type level) there are products with 0 as price but with weight and they are taken into account in this calculation, , the calculation will be skewed.

What calculation should I use not to include rows where weight is not a text or error and price is not 0?

Thanks

Ewa

[v-juanli-msft · Accepted solution]

Hi @ewuchatka

Use the following formula in calculated columns

usefulweight = IF([Weight]="unidentified"||[Price]=0,BLANK(),[Weight])

usefulprice = IF([Price]=0||[Weight]="unidentified",BLANK(),[Price])

£/kg per product =
CALCULATE ( SUM ( Sheet7[usefulprice] ), ALLEXCEPT ( Sheet7, Sheet7[Product] ) )
    / CALCULATE (
        SUM ( Sheet7[usefulweight] ),
        ALLEXCEPT ( Sheet7, Sheet7[Product] )
    )
    * 1000

Best Regrads

Maggie

[Ashish_Mathur]

Hi,

Create a calculated column

Revised weight=IF(ISNUMBER('Power BI'[Weight]),'Power BI'[Weight],BLANK())

Now write this measure

£/kg = IFERROR(IF(sum('Power BI'[Price per Unit])/sum('Power BI'[Revised Weight])*1000=0,BLANK(),sum('Power BI'[Price per Unit])/sum('Power BI'[Revised Weight])*1000),BLANK())

Hope this helps.

[MFelix]

Hi @ewuchatka,

You need to create a measure similar to this:

Total =
CALCULATE (
    SUM ( Table[Price per unit] ) / SUM ( Table[Weight] )
        * 1000;
    Table[Price per unit] <> 0
        || Table[Weight] <> "unindentified"
)

Replace the columns by the ones you want to sum and filter.

Regards,

MFelix