cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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"

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())
'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.