Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@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.
@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.
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 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....
Proud to be a PBI Community Champion
Hi, Target values are defined by me (hardcoded). Of course, in reality I have much more USERs.
SUM ORD is basic calculation of values from source table (column # ORD) = In souce table we have 5 records, 3 out of them for U1, so SUM of column '# ORD' is 5+10+5 = 20.
thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |