Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |