cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

If two conditions are met then use measure

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

Re: If two conditions are met then use measure

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
```

Maggie

3 REPLIES 3
Super User

Re: If two conditions are met then use measure

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

Proud to be a Datanaut!

Super User

Re: If two conditions are met then use measure

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.

Community Support Team

Re: If two conditions are met then use measure

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
```