Linking two fields

[Anonymous]

I have the following bar chart:

I need to augment the above chart with data from another table with information as provided in the straight table below:

The 'DMF Subject' field in the bar chart is the equivalent of the 'Active Ingredient' in the straight table above, for example the 'Pregabalin' circled in the two charts above.  However, in some cases, these two field values do not match exactly in spelling.  I need to be able to add a second expression to the bar chart which gives the '2015 Revenues' for the  'DMF Subject' based on the corresponding 'Active Ingredient' '2015 Revenue' from the straight table.

How can this be accomplished?

Regards

Chris

[amayuresh]

Please provide Sample Data

[Digvijay_Singh]

Try something like this, you may need two different scale to plot two measures..

Sum({<Year={2015},[DMF Subject]={"=[DMF Subject]=[Active Ingredient]">}Revenue)

[Anonymous]

Many thanks.  My expression as per your recommendation is as follows:

However the chart gives the same values for the revenue plot as follows:

The revenues shown are the totals for all the products in the data set.

Regards

[Anonymous]

Dear Mauresh

Please find attached sample data.  The Annual Reports Summary provides the revenue figures and the second file gives the DMF data.  For the DMF data, use the 'Type' II records only.

Regards.

[Digvijay_Singh]

How are you linking both the data, is it possible to keep common name  for both these fields? Currently the case is different for both the fields so they won't match. They need to be transformed to same case for comparison, lower() or Upper() function.

AFAIK if both fields have same name in data model, when you take dimension as DMF Subject then automatically it will pick revenue record from same Active ingredient.

[Digvijay_Singh · Accepted solution]

Check if attached can help. I kept same name for subject/Ingredient.I may be wrong from my little understanding of the subject.

[Digvijay_Singh]

Oh..missed the attachment, here it is -

[Anonymous]

Many thanks, that works