cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

## Create new column with multiple IF criteria and multiply

I want to add a new measure (Competing Avg Rate) to my data which gives me average rate for competing offers.

Ive tried this:
Competing Avg Rate (post) =
CALCULATE(DIVIDE('DATA'[Funded Elsewhere SUM(£)],[Competing Offer w/average rate],""),filter(DATA,DATA[Competing Offer]="yes"))
i have had no luck with this as it doesnt give me the correct values.

it contains:
Calculated column - Competing Offer w/average:
CALCULATE('DATA'[Funded Elsewhere - Loan Amount]) * SUM('DATA'[Funded Elsewhere - Rate]),DATA[Funded Elsewhere - Rate]<>0,DATA[Unsecured Offer 1 Amount]<>0,DATA[Competing Offer]="Yes")
this should translate to: [Funded Elsewhere - Loan Amount] by  [Funded Elsewhere - Rate] IF Competing offer = Yes, Funded elsewhere rate is not 0 and unsecured offer 1 amount is not 0

Measure - Funded Elsewhere SUM(£) = CALCULATE(SUM('DATA'[Funded Elsewhere - Loan Amount]),DATA[Funded Elsewhere - Rate]<>0,DATA[Unsecured Offer 1 Amount]<>0,DATA[Competing Offer]="Yes")

Competing Offer w/average rate =
CALCULATE('DATA'[Funded Elsewhere - Loan Amount]) * SUM('DATA'[Funded Elsewhere - Rate]),DATA[Funded Elsewhere - Rate]<>0,DATA[Unsecured Offer 1 Amount]<>0,DATA[Competing Offer]="Yes")

The excel formula is:
=SUMIFS (\$AR:\$AR,
\$AT:\$AT,"Yes",
!\$N:\$N,"<>"&"",
!\$X:\$X,">"&"")
/ SUMIFS (\$W:\$W,
,\$AT:\$AT,"Yes",
\$N:\$N,"<>"&"",
\$X:\$X,"<>"&"")

AR =Competing Offer w/average rate (dec number)
AT= Competing Offer (text)
N =Unsecured Offer 1 Amount
X =Funded Elsewhere - Rate (%)

SEND HELP!! 😣
Super User

Can you please share some example data and explain what you need to calculate? It's really difficult not to get lost with all these information and formulas without any context.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors