cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Multiplying across related tables with a qualifier

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III

## Re: Multiplying across related tables with a qualifier

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 =
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

4 REPLIES 4
Super User III

## Re: Multiplying across related tables with a qualifier

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 =
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

Super User III

## Re: Multiplying across related tables with a qualifier

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

New Contributor

## Re: Multiplying across related tables with a qualifier

Please refer the PBIX with out DAX.

If this post helps, then please consider Accept it as the solution to help the other members find it more

If this post was helpful may I ask you to mark it as solution and give it some kudos?

Frequent Visitor

## Re: Multiplying across related tables with a qualifier

Thank you so much!  That worked beautifully.  Cheers

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors