cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ewuchatka Regular Visitor
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
Community Support Team

Re: If two conditions are met then use measure

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

6.png

 

Best Regrads

Maggie

3 REPLIES 3
Super User
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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
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
Community Support Team

Re: If two conditions are met then use measure

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

6.png

 

Best Regrads

Maggie