How to combine different dimension in an object

[nayanqlik]

Hi

For simplicity, I have the 2 tables below in my front end (Table1 & Table 2).  The one is Sales by Manager whereas the other is Survey Count by Survey Manager.  The values in the Manager and Sales Manager fields are the same person. I want to create a table in the front end where Manager and Survey Manager are linked. (see below table Correct).

However, my table is not displaying correct (see table marked Incorrect)

Please note, I don't want to link it in the backend as it has implications on other parts of the model.

So, can this be solved in the frontend only.

Your assistance is appreciated.

Kind regards

Nayan

Table 1

Manager	Sales
A	20
B	30
C	60

  Table 2

Survey Manager	Survey Count
A	6
B	10
C	15

Correct

Sales Manager	Sales Manager Survey	Sales	Survey Count
A	A	20	6
B	B	30	10
C	C	60	15

Incorrect

Sales Manager	Sales Manager Survey	Sales	Survey Count
A	A	20	6
A	B	20	10
A	C	20	15
B	A	30	6
B	B	30	10
B	C	30	15
C	A	60	6
C	B	60	10
C	C	60	15

[sunny_talwar]

May be just do this

If([Manager] = [Survey Manager], Sum(Sales))

If([Manager] = [Survey Manager], Sum([Survey Count]))

[nayanqlik]

Hi Sunny

Thank you for your quick response.  I will try and let you know.

Kind regards

Nayan

[sunny_talwar · Accepted solution]

Check the attached

[Digvijay_Singh]

May be try like this -

[nayanqlik]

Hi Sunny

It worked.  Another twist.  I have a lower level dimension.  Employee which falls under Manager and Survey Employee falling under Survey Manager.

So will the formula be

If([Manager] = [Survey Manager] or [Employee] = [Survey Employee], Sum(Sales))

If([Manager] = [Survey Manager] or [Employee] = [Survey Employee],, Sum([Survey Count]))

Kind regards

Nayan

[sunny_talwar]

May be using and instead of or....

[qv_testing]

HI,

why don't you make associate 2 tables with Manager.

Like

Table1:

LOAD * INLINE [

    Manager, Sales

    A, 20

    B, 30

    C, 60

];

Table2:

LOAD *,  [Survey Manager] as Manager;

LOAD * INLINE [

    Survey Manager, Survey Count

    A, 6

    B, 10

    C, 15

];

Then you can use simple expression.

Sum(Sales)

sum(Survey Count)

[nayanqlik]

Hi Digvijay

Your formula worked too.  See my response to Sunny, regarding a lower level of staff. I have an  Employee which falls under Manager and Survey Employee falling under Survey Manager.

Should i apply similar formula to the one you have given for manager.

Kind regards

Nayan