1 Reply Latest reply on Oct 6, 2016 9:44 AM by Joe Oppelt

# Combining Data Sources and Creating Calculated Fields

Hello,

I have two data sources which I have combined via blending. The second data source is a reference table. In the primary data source I have individual events that I am tracking with pricing information for each event. Each event has a billing status and based on that status and a few other things, I am trying to calculate the prices based on whether or not the event is complete, and if it is not complete then I use the reference table to multiple a percentage with the price of that event. Example:

Event (unique #)
Plan
Type
Complete
Price
1ABCAYES5
2XYZBYES4
3DEFAYES4
4ABCBNO3
5DEFBYES2
6DEFANO6
7XYZANO7

Then my reference table would look something like this:

PlanType
Factor
ABCA0.75
ABCB0.67
DEFA0.56
DEFB0.50
XYZA0.80
XYZB0.75

So if the event is not complete, then I want the price in the original data source to be multiplied by the factor. Then I need to aggregate data by plan and sum the total dollar amount for the prices of the events for that plan. In my current view, it is not summing the data properly. It seems like the Factor from the reference table is coming in as an aggregate field and that it is not actually joined to my primary data source as another column of data. Thus, I can't even look at the underlying data and see where the calculation is going wrong.

• ###### 1. Re: Combining Data Sources and Creating Calculated Fields

Everything coming from a secondary blended data source has to come as an aggregated value.

If you have only one row for the join-field, then SUM([Value]) will give you that value for each key value in your primary source.

So if you are blending on [Plan] and [Type], then SUM([secondary].[Factor]) = 0.75 for ABC-A in your underlying data table.  (If there were two rows for ABC-A in the secondary source, it would sum the two [Factor] values together.  If you don't want them summed, you could instead use MAX() or MIN() or AVG() instead of SUM.)

If you want, you could create a calc field in your primary source that does the SUM([secondary].[Factor], but it's not really necessary.