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
- PattemManohar
Community Champion
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....
- az38
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.
- az38
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
v-yuta-msftCommunity Support
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.
