cancel
Showing results for
Search instead for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper V

## Calculating Average with Filters

I need to calculate the "average deal size" by year.  Basically, how much \$ on average was each opportunity worth. I came up with a measure that produced values but I'm not sure if they are accurate:

AvgCompanyMRR Deal Size = DIVIDE([CompanyMRR\$], CALCULATE(COUNTA('dpmgr vwJMWebSalesOpportunitiesProduction'[OpportunityID]), 'dpmgr vwJMWebSalesOpportunitiesProduction'[MRRNormalized\$] > 0, 'dpmgr vwJMWebSalesOpportunitiesProduction'[JobNum] <> BLANK(), SalesLeads[LeadOrigination]= "CompanyGen"), 0)

Would this give me the average MRR\$ of an Opportunity for CompanyGen Leads?

To simplify: need the Average MRR\$ IF LeadOrigination= "CompanyGen"

1 REPLY 1
Community Support

Hi @bhmiller89,

I have modified your formula, you can try it if it works on your side:

```AvgCompanyMRR Deal Size =
DIVIDE(
[CompanyMRR\$],
COUNTA(
Filter('dpmgr vwJMWebSalesOpportunitiesProduction',
AND('dpmgr vwJMWebSalesOpportunitiesProduction'[MRRNormalized\$] > 0,
'dpmgr vwJMWebSalesOpportunitiesProduction'[JobNum] <> BLANK())
&&Related( SalesLeads[LeadOrigination])= "CompanyGen"),
'dpmgr vwJMWebSalesOpportunitiesProduction'[OpportunityID])
, 0)```

If above is not help, can you share me the sample data and provide the tables relationships of this?

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

## Helpful resources

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors