Forum Discussion
Report Builder: How to create a Calculated Field using data from 2 different Data Sets
Hi,
I'm very familiar with Power BI Desktop but new to Power BI Report Builder.
My question is: Is it possible to create a calculated field in Power BI Report Builder using data from 2 different Data Sets?
For example, I'd like to create a calculated field like the following:
PMPM
= ((Sum(Fields!PlanPaid.Value, "Claims_Rolling12")) / (CountDistinct(Fields!EmployeeID.Value, "Eligibility_Rolling12")))
It would be using the Sum of [PlanPaid] from my "Claims_Rolling12" Data Set and dividing it by the Distinct Count of [EmployeeID] from my "Eligibility_Rolling12" Data Set.
I would need the table to look like this:
Both the Claims_Rolling12 Data Set and Eligibility_Rolling12 Data Set have a column called Date that is formatted the same. I'm just having trouble creating the Calculated Field.
Is this type of thing possible in Report Builder?
2 Replies
- AnonymousNot applicable
Hi, yes it is possible.
You need to link the two tables in the data model to create relationship between them so the filters can propagate.
- V-lianl-msftCommunity Support
Hi Anonymous ,
You cannot add the twor results directly. You need to do it using a custome code in SSRS.
Hope these links will help you:
http://www.bidn.com/blogs/MarkGStacey/bidn-blog/2714/aggregated-lookups-in-ssrs-2008r2
http://stackoverflow.com/questions/8093443/how-to-combine-aggregates-within-a-group-with-aggregates-across-groups-within-ss
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.