Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have one table like this:
Area | Location | Value
and anther table like this:
Area | Location | Value | Worth
What I want to do is return the worth value. However, I cannot create a relationship as I need to lookup the SUM of the value from the first table. For example:
I may have the following data set
Area | Location | Value
AK | NZ | 10
AK | NZ | 5
AK | AU | 4
What I need to do is not return the worth on each row - so I don't want to look up the 10 and 5 for the NZ location, instead I want to lookup 15 (the SUM of the values). The end table I want to create would look like this - it would be a summary:
AK | NZ | 15 | 500
Does anyone have any ideas how I could achieve this?
Solved! Go to Solution.
Hi @WESTi,
In this scenario, you can first summarize your source table.
NewTable1 = SUMMARIZE ( 'Return Value Table1', 'Return Value Table1'[Area], 'Return Value Table1'[Location], "Total Value", SUM ( 'Return Value Table1'[Value] ) )
In 'NewTable1', get the worth value from second table via LookUpValue.
Return Worth = LOOKUPVALUE ( 'Return Value Table2'[Worth], 'Return Value Table2'[Area], NewTable1[Area], 'Return Value Table2'[Location], NewTable1[Location], 'Return Value Table2'[Value], NewTable1[Total Value] )
Best regards,
Yuliana Gu
Hi @WESTi,
In this scenario, you can first summarize your source table.
NewTable1 = SUMMARIZE ( 'Return Value Table1', 'Return Value Table1'[Area], 'Return Value Table1'[Location], "Total Value", SUM ( 'Return Value Table1'[Value] ) )
In 'NewTable1', get the worth value from second table via LookUpValue.
Return Worth = LOOKUPVALUE ( 'Return Value Table2'[Worth], 'Return Value Table2'[Area], NewTable1[Area], 'Return Value Table2'[Location], NewTable1[Location], 'Return Value Table2'[Value], NewTable1[Total Value] )
Best regards,
Yuliana Gu
Can you clarify if you are hoping to add a calc column to 1 of your table, create a new table, or are we talking about just creating measures and visuals?
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |