Create a calculation in a table visual, not in underlying data

[JoeCrozier]

I asked this question previously on stack overflow, but never really got a FINAL answer, though I did realize (due to some of the comments) that I needed to refine my question a little:

In a previous Power Bi dashboard I was working on, I created a variable called "Percent of Planned Pace". It showed whether or not a study protocol was accruing patients as fast as it was supposed to.

Lets take an imaginary scenario like this:

• Study named LS-P-Joe
• Total target patients: 100
• Total years planned: 10, So 10 patients per year
• Months since activation: 24 (2 years)
• Total patients enrolled so far 15

So in this case its real simple, since its 100 patients over 10 years, that means we're expecting ten per year. Its been 2 years, so we'd expect 20 so far... but we're at 15 so we're a little behind the pace.

In a previous dashboard (and underlying dataset in power query) I created the "percent of planned pace" variable with this: 

Well that works great when its one data source that I can add a column to.

My problem now is that my data no longer comes from one source. It comes from several. So I need to create a measure or something that calculates this for each row in my visual, but each column might be from a different query:

In this example from above, I've added in parenthesis what each variable is called and what dataset it is from. How can I calculate this for my visual?

• Study named LS-P-Joe (rv_protocol_basic:protocol_no)
• Total target patients: 100 (rv_protocol_dtl_accrual_info:protocol_target_accrual)
• Total years planned: 10 (rv_protocol_dtl_accrual_info:total_years_planned) So 10 per year
• Months since activation: 24 (2 years) (rv_protocol_basic:months_since_activation)
• Total patients enrolled so far 15 (rv_protocol_basic: accrual_to_date)

I'm assuming (given its across queries) that it'd be a measure, but my attempts so far have been terrible.  I've tried this (pictured) but given all the squiggly lines, clearly not working:

[v-yanjiang-msft · Accepted solution]

Hi @JoeCrozier ,

In your foumula of the new created measure, there're two errors. Firstly an additional parenthesis is added at the end.

Then in a measure, if you want to reference a column, a MAX/SUM should be added before the column name like this MAX( [Column] ). Please modify it and try again.

Best regards,

Community Support Team_yanjiang

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

[foodd]

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.