Forum Discussion
Trouble Calculating Measures Using Multiple Tables
I am trying to create a measure that will calculate the target number of accounts and leads to show in several card visualizations in order to maintain our marketing/sales pipeline, but I'm running into problems building the measure due to the relationships, so I'm wondering if I can get any insight/suggestions here.
Here's a little background on what I'm trying to do before factoring the relationships into the equation.
- Our benchmark here is that there needs to be 6 accounts per business unit qualified each month. This is the single static number in the measure since even our number of business units change throughout a given year.
- For the particular "bucket" in question (Account Score 1-40), we want to have a pipeline of leads moving through on a consistent basis and the amount in the bucket is determined by multiplying 6 by the number of months it takes an account to reach a total score of 40 points (at which point, it's an MQL). This is in the CRM-Leads table as a calculated column per lead
Time to 40 =
DATEDIFF (
'CRM-Leads'[createdon].[Date],
'CRM-Leads'[fsr_leadscore50date].[Date],
MONTH
)
and expressed in the visualization as an average. (Yes, I'm aware we're measuring account score movement on the leads table, but account score is a calculated column without a timestamp, so I can't do a DATEDIFF calculation there).
So the simple calculation would be
Account 1-40 Target =
6 * ( AVERAGE ( 'CRM-Leads'[Time to 40] ) ) - The measure MUST be dynamic and change depending on the level of assignment clicked (all, regional, business unit), therefore I need to calculate the measure per business unit so it can be sliced the different ways.
Total Account 1-40 Target =
COUNT ( 'CRM-Business Units'[name] )
* ( 6 * ( AVERAGE ( 'CRM-Leads'[Time to 40] ) ) ) - To further complicate things, I then need to dynamically calculate the target number of leads in the leads 1-40 bucket, which is done by multiplying the above calculation by the average number of board members, which is a measure in my CRM-Accounts table
Average # of Board Membersper Account =
DIVIDE (
DISTINCTCOUNT ( 'CRM-Leads'[leadid] ),
( DISTINCTCOUNT ( 'CRM-Accounts'[accountid] ) )
)
I believe that calculation would be:
Total 1-40 Leads Target =
'CRM-Account'[Average # of Board Membersper Account]
* (
COUNT ( 'CRM-Business Units'[name] )
* ( 6 * ( AVERAGE ( 'CRM-Leads'[Time to 40] ) ) )
)
HOWEVER, as you can see from the screenshots, the way that the relationships currently work, I can't figure out a way to pull these formulas together in a way that works. Do I need to create a separate table for this? Should I set up my relationships differently (all are set up as many:1 with many in the left column, with the exception of business units to assignments, which is 1:1)?
I'm still relatively new to Power BI, so I'm at a bit of a loss. Any suggestions would be EXTREMELY helpful. Please let me know if you need screenshots or further explanation of something. With the exception of sharing PII, I'm happy to provide details.
4 Replies
- AlexisOlsonSuper User
How are you determining whether it "works" or not? To an outsider, those are just numbers in the screenshot and it isn't immediately clear whether they are correct or wildly wrong.
- kellyyakerFrequent Visitor
We've validated the data through our CRM outside of PBI. I can pull a list of accounts with a score of 1-40, list of leads 1-40, etc. Based on that number, I feel comfortable saying that the calculations in the card visualizations.
- AlexisOlsonSuper User
My point wasn't that I don't believe you about it being wrong but rather that without knowing what kind of values you expect to see and how they differ from what you are seeing (and why they should be different), it's difficult to make any useful suggestions.
- AnonymousNot applicable
Hi kellyyaker,
Can you please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng