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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.