Skip to main content
cancel
Showing results for 
Search instead 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

Reply
sbarker_11
Helper I
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!! 😣
1 REPLY 1
mlsx4
Super User
Super User

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. 

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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