Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Donald Fox]

I am trying to put together a Measure that calculates a pct. of Budget based on data that is not within the table. 
I am taking revenue from Table A based on a filter for Table C  and dividing it by revenue in Table B that is based on a filter that is, also, in Table B.

Here is what I have so far > 

% of BudgetNew 20 = FILTER(RELATEDTABLE(qry_New_Lost),qry_New_Lost[ClientStatus] = “New”)),SUM((‘QRY_Actuals_New_rpt'[FY20 Rev])/CALCULATE(FILTER(QRY_Budget_New_rpt,SEARCH(“NEW%”,QRY_Budget_New_rpt[PClientID],QRY_Budget_New_rpt[FY20 Budget])))

Want to do is to take the revenue amount from QRY_Actuals_New and filtering it by Client Status in qry_New_Lost and dividing it into the Budeted Revenue amount from QRY_Budget)New_rpt that is filtered based on PClientID (If it starts with “NEW…)

Power BI doesn’t like the part in bold.  What am I doing wrong?
Don

——————————
Donald Fox
BI Developer
——————————

[Christopher Schnaars]

Hi, :

I’m having trouble following what you’re doing, but I see two things: First, I think Power BI doesn’t like your formula becuase you have mismatched closing parentheses. Before the bold-faced code, I see two opening parentheses and three closing parentheses.

Also: I am no expert on CALCULATE, but don’t you need the formula first and then the filter? I also think that if your relationships are defined correctly, you might not need things like RELATEDTABLE. I’m not sure about the second part of your formula, but I think the first part (before the division operator) should be something like this:
CALCULATE(SUM(‘QRY_Actuals_New_rpt'[FY20 Rev]), qry_New_Lost[ClientStatus] = “New”)

Hopefully that will get you started.
?

——————————
Christopher Schnaars
Laboratory Testing Inc.
Hatfield, Pennsylvania
——————————
——————————————-

[Donald Fox]

Thank you Christopher,
I was able to come up with the following calculation that, logically works but is not given me the correct calculation.

% of Budget20New = CALCULATE(SUM(‘QRY_Actuals_New_rpt'[FY20 Revenue]),QRY_Actuals_New_rpt[AClient Status]= “NEW”) / CALCULATE(SUM(‘QRY_Budget_New_rpt'[FY20 Bud]),(LEFT(QRY_Budget_New_rpt[PClientID],3)= “NEW”))

Still working through this.
Don

——————————
Donald Fox
BI Developer
——————————
——————————————-

[Christopher Schnaars]

If you haven’t figured this out yet, check your table relationships to make sure they are correct, and check any filters on the visual or the page that might be monkeying with your results. For testing, you also could break your formula into two pieces (one for numerator and one for denominator) to make sure you’re getting the results you expect and to help isolate the problem.

——————————
Christopher Schnaars
Laboratory Testing Inc.
Hatfield, Pennsylvania
——————————
——————————————-

[Donald Fox]

Thank you Christopher,
We have revamped the model, partly, based on what you advised. We knew that our relationships are hosing us up.
Thank you for the advice.
Don

——————————
Donald Fox
BI Developer
——————————
——————————————-