Calculating Average with Filters

[bhmiller89]

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"

[Anonymous]

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