Forum Discussion
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
- fooddCommunity Champion
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).
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.
- v-yanjiang-msftCommunity Support
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.