Countifs across related tables

[AbbasAsaria90]

I have the following tables:

• pd_nb_leads : each row corresponds to a sales lead created in a time period, one of the columns is the deal origin (i.e., sales channel) which is returned in an integer format
• pd_dealorigin_metadata : this relates the integer with a text string (i.e., what the deal origin is in our CRM system)
• Origin Mapping : This categorises the deal origins into a smaller number of main categories
• Targets : This gives a target for each of the main categories in the Origin Mapping table

What I would like to do is show a percentage graph of how many leads have been created against the targets for each main category. By creating the adequate relationships between the four tables I have been able to show progess in absolute numbers (above) using a line and stacked column chart, but given the targets for each main category differ so much that gives less insight into the smaller categories

One solution I have in mind is having a calculated column in the Targets table to "Countif" the rows in the first table against their main category which appears in the Targets table, and having a second calculated column to look at the percentage. It's the first calculated column (the countif equivalent) that I'm having trouble with

Any suggestions? Thanks very much

[v-jiascu-msft]

Hi @AbbasAsaria90,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[AbbasAsaria90]

Ah sorry about that - thanks it worked great!

Best,

AA

[v-jiascu-msft · Accepted solution]

@AbbasAsaria90,

Hi AA,

You can try this formula. 

Measure 2 =
DIVIDE ( COUNT ( 'pd_nb_leads'[id] ), SUM ( Targets[target] ), 0 )

Best Regards!

Dale

[Greg_Deckler]

The general equivalent for COUNTIF is CALCULATE with a filter. In your case you would likely have to use a RELATED or RELATEDTABLE as part of your FILTER clause in your CALCULATE. Tough to be specific without sample data.

[AbbasAsaria90]

@Greg_Deckler

Thanks for your help! 

I've shared the pbix file here (https://www.dropbox.com/s/42sa3rwlrvxtyzy/170831%20sample%20leads%20file.pbix?dl=0) - if that helps

Best,

AA