Forum Discussion
sbarker_11
Helper I
Helper ICreate 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:
Calculated column - Competing Offer w/average:
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.
i have had no luck with this as it doesnt give me the correct values.
it contains:
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,">"&"")
!$X:$X,">"&"")
/ SUMIFS ($W:$W,
,$AT:$AT,"Yes",
$N:$N,"<>"&"",
$X:$X,"<>"&"")
AR =Competing Offer w/average rate (dec number)
AT= Competing Offer (text)
AT= Competing Offer (text)
N =Unsecured Offer 1 Amount
X =Funded Elsewhere - Rate (%)
SEND HELP!! 😣
1 Reply
mlsx4Memorable Member
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.