CODEWITHSUNDEEP
powerbipowerquery
Abhishek Govilkar
Abhishek Govilkar

Reputation: 27

Calculation based on condition in Power-BI

I have a data table that I would like to make a new column based on the calculation with certain conditions.

A sample of my dataset is as below:

enter image description here

I would like to create a new column "Retailer Margin" with a formula as below:

Retailer Margin = [MRP] - [Retailer Landed Price]

As my data for retailer landed price also have some null (0) values and I want the condition that in such cases calculation should not happen and it should give a "Null" value.

The expected information for new column should be like following:

enter image description here

Any help on the same will be highly appreciated.

Thanks in advance!

Upvotes: 0

Views: 363

Answers (1)

Andrey Nikolov
Andrey Nikolov

Reputation: 13450

You can either create a custom column in Power Query Editor using M, or a calculated column using DAX. The code for each of these is as follows.

Custom column:

Retailer Margin (M) = if ([Retailer Landed Price] <> 0) then [Maximum Retail Price] - [Retailer Landed Price] else null

enter image description here

Computed column:

Retailer Margin (DAX) = IF('Table'[Retailer Landed Price] <> 0, 'Table'[Maximum Retail Price] - 'Table'[Retailer Landed Price], BLANK())

enter image description here

In your case it doesn't really matters which one you will choose. The custom column is more logical choice, though.

Upvotes: 1

Related Questions