Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AbbasAsaria90
Helper I
Helper I

Countifs across related tables

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

170831 two graphs lead progress.png

 

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

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

@AbbasAsaria90,

 

Hi AA,

 

You can try this formula. 

 

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

Countifs across related tables.jpg

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-jiascu-msft
Employee
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ah sorry about that - thanks it worked great!

 

Best,

AA

v-jiascu-msft
Employee
Employee

@AbbasAsaria90,

 

Hi AA,

 

You can try this formula. 

 

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

Countifs across related tables.jpg

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.