cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.