Create new column with multiple IF criteria and multiply

[sbarker_11]

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!! 😣

[mlsx4]

Hi @sbarker_11 

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.