Forum Discussion
Add target value to other table
Hi guys,
I have following source table:
| ID | USER_ID | # ORD |
| 1 | U1 | 5 |
| 2 | U1 | 10 |
| 3 | U1 | 5 |
| 4 | U2 | 50 |
| 5 | U3 | 1 |
Then I also have additional table with target values:
| USER_ID | Target |
| U1 | 25 |
| U2 | 50 |
What I'd like to do is following:
| USER_ID | SUM ORD | Target |
| U1 | 20 | 25 |
| U2 | 51 | 50 |
Could you please advise me how to add target value (for each USER_ID) to my visualisation (based on source table data).
Thanks in advance
Anonymous ,
So if the USER_ID does not exist in Source table, it can be also understanded as the max(lastest) USER_ID in additional table, right? If so, you can create a new USER_ID column in Source table using DAX below:
New USER_ID = IF('Source Table'[USER_ID] = RELATED('Additional Table'[USER_ID]), RELATED('Additional Table'[USER_ID]), MAX('Additional Table'[USER_ID]))Then you can create a measure based on the new USER_ID column to achieve the total result.
SUM #ORD = CALCULATE(SUM('Source Table'[# ORD]), ALLEXCEPT('Source Table', 'Source Table'[New USER_ID]))Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Anonymous Could you please confirm - how your final expected has 51 for USER_ID U2. Is it you are adding U2 and U3 data ? If yes, what is your SUM ORD field logic....
If I understand correct you should just add relationship between tables, then add to visual fields USER_ID, Sum of #ORD and average (or max, or min, whatever) Target
Yes, you're right. That's the easiest solution. But what can happen is that I have in target table USER1, USER2, but in source table records for USER1, USER2, USER3, USER4. Then if I use USER_ID from source table, relation doesn't work correctly and if I take it from target, it works, but shows only USER1, USER2 and rest is shown as blank.
For your example from start post it will look like this
you have no target for U3 in target table, so, you have a blank target in visual
Anonymous ,
So if the USER_ID does not exist in Source table, it can be also understanded as the max(lastest) USER_ID in additional table, right? If so, you can create a new USER_ID column in Source table using DAX below:
New USER_ID = IF('Source Table'[USER_ID] = RELATED('Additional Table'[USER_ID]), RELATED('Additional Table'[USER_ID]), MAX('Additional Table'[USER_ID]))Then you can create a measure based on the new USER_ID column to achieve the total result.
SUM #ORD = CALCULATE(SUM('Source Table'[# ORD]), ALLEXCEPT('Source Table', 'Source Table'[New USER_ID]))Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
