Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Check the attached
May be just do this
If([Manager] = [Survey Manager], Sum(Sales))
If([Manager] = [Survey Manager], Sum([Survey Count]))
Hi Sunny
Thank you for your quick response. I will try and let you know.
Kind regards
Nayan
Check the attached
May be try like this -
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
May be using and instead of or....
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)
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