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 - I am fairly new to PowerBI and I'm trying to use a measure to calculate a company's exposure to an underlying investment. Basically, I'd like to filter for a given company, and have Power BI calculate their exposure to the underlying investments. Simplistically, there are two related tables that have a Ticker symbol as the common dimension:
Table 1 | ||
Company | Ticker | % Owned |
A | CCC | 10% |
A | DDD | 15% |
B | CCC | 5% |
B | DDD | 10% |
Table 2 | ||
Ticker | Investment 1 | Market |
CCC | 100 | Atlanta |
CCC | 200 | DC |
CCC | 300 | Seattle |
DDD | 100 | San Francisco |
DDD | 200 | Chicago |
DDD | 300 | NYC |
And I would like to use a filter to select Company A, and I would get back the following information as shown in Table 3:
Table 3 | ||||
Company | Ticker | Ownership | Market | Value |
A | CCC | 10% | Atlanta | 10 |
A | CCC | 10% | DC | 20 |
A | CCC | 10% | Seattle | 30 |
A | DDD | 15% | San Francisco | 15 |
A | DDD | 15% | Chicago | 30 |
A | DDD | 15% | NYC | 45 |
Total | 150 |
Thank you in advance for any help you can provide!
Solved! Go to Solution.
Hi @fcanney
You could create a new calculated table with all you want and then use a slicer to select the company
1. Create a new table:
NewTable = ADDCOLUMNS ( GENERATE ( Table1; CALCULATETABLE ( SUMMARIZE ( Table2; Table2[Investment 1]; Table2[Market] ); FILTER ( ALL ( Table2[Ticker] ); Table2[Ticker] = CALCULATE ( DISTINCT ( Table1[Ticker] ) ) ) ) ); "Value"; [Ownership] * [Investment 1] )
2. Place all the columns you want to see in a table visual. Make sure they are all set to "Don't summarize"
3. Use a slicer on NewTable[Company] to select company as needed
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Hi @fcanney
You could create a new calculated table with all you want and then use a slicer to select the company
1. Create a new table:
NewTable = ADDCOLUMNS ( GENERATE ( Table1; CALCULATETABLE ( SUMMARIZE ( Table2; Table2[Investment 1]; Table2[Market] ); FILTER ( ALL ( Table2[Ticker] ); Table2[Ticker] = CALCULATE ( DISTINCT ( Table1[Ticker] ) ) ) ) ); "Value"; [Ownership] * [Investment 1] )
2. Place all the columns you want to see in a table visual. Make sure they are all set to "Don't summarize"
3. Use a slicer on NewTable[Company] to select company as needed
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Thank you so much! That worked beautifully. Cheers
See what was described above at work in the attached file.
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
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 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |