Report Builder: How to create a Calculated Field using data from 2 different Data Sets

[Anonymous]

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?

[V-lianl-msft]

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.

[Anonymous]

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.