Forum Discussion

bhmiller89's avatar
bhmiller89
Helper V
9 years ago

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"

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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