Forum Discussion

JoeCrozier's avatar
JoeCrozier
Helper II
3 years ago
Solved

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

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:

 

  • 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.

2 Replies