cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gerhigrabun
New Member

Linking two matrix with different data

Hello,

Is there a way where i can link two different matrix with one slicer. If i select the Op Inc on the slicer i want the first matrix to show AOI which i have working. The trouble im having is that I want the second matrix to show AOI%. Is there a way that i can make it when the first matrix is AOI the second one automatically goes to AOI%? 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @gerhigrabun ,

Here are the steps you can follow:

1. Create calculated table.

Slice =
DISTINCT('Table'[Attribute])

2. Create measure.

Measure =
var _select =SELECTEDVALUE('Slice'[Attribute])
return
CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Quarter]=MAX('Table'[Quarter])&&'Table'[Year]=MAX('Table'[Year])&&'Table'[Attribute]=_select))
Measure 2 =
var _select =SELECTEDVALUE('Slice'[Attribute])
return
SWITCH(
    TRUE(),
_select="Operating Income",CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Attribute]="AOI%"&&'Table'[Quarter]=MAX('Table'[Quarter])&&'Table'[Year]=MAX('Table'[Year]))), 
 _select="EBITDA",CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Attribute]="EBITDA%"&&'Table'[Quarter]=MAX('Table'[Quarter])&&'Table'[Year]=MAX('Table'[Year]))))

3. Use the [Attribute] of the Slice table as a slicer, create two matrices, and put them into Measure and Measure2 respectively

Slice:

vyangliumsft_0-1626915231095.png

Matrix 1:

vyangliumsft_1-1626915231097.png

Matrix 2:

vyangliumsft_2-1626915231099.png

4. Result:

Select "Operating Income" in the slicer, the value of "Operating Income" is displayed in matrix 1, and the value of "AOI%" is displayed in matrix 2.

vyangliumsft_3-1626915231101.jpeg

Select "EBITDA" in the slicer, the value of "EBITDA" is displayed in matrix 1, and the value of "EBITDA %" is displayed in matrix 2.

vyangliumsft_4-1626915231102.jpeg

 

Best Regards,

Liu Yang

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

View solution in original post

5 REPLIES 5
v-yangliu-msft
Community Support
Community Support

Hi  @gerhigrabun ,

Here are the steps you can follow:

1. Create calculated table.

Slice =
DISTINCT('Table'[Attribute])

2. Create measure.

Measure =
var _select =SELECTEDVALUE('Slice'[Attribute])
return
CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Quarter]=MAX('Table'[Quarter])&&'Table'[Year]=MAX('Table'[Year])&&'Table'[Attribute]=_select))
Measure 2 =
var _select =SELECTEDVALUE('Slice'[Attribute])
return
SWITCH(
    TRUE(),
_select="Operating Income",CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Attribute]="AOI%"&&'Table'[Quarter]=MAX('Table'[Quarter])&&'Table'[Year]=MAX('Table'[Year]))), 
 _select="EBITDA",CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Attribute]="EBITDA%"&&'Table'[Quarter]=MAX('Table'[Quarter])&&'Table'[Year]=MAX('Table'[Year]))))

3. Use the [Attribute] of the Slice table as a slicer, create two matrices, and put them into Measure and Measure2 respectively

Slice:

vyangliumsft_0-1626915231095.png

Matrix 1:

vyangliumsft_1-1626915231097.png

Matrix 2:

vyangliumsft_2-1626915231099.png

4. Result:

Select "Operating Income" in the slicer, the value of "Operating Income" is displayed in matrix 1, and the value of "AOI%" is displayed in matrix 2.

vyangliumsft_3-1626915231101.jpeg

Select "EBITDA" in the slicer, the value of "EBITDA" is displayed in matrix 1, and the value of "EBITDA %" is displayed in matrix 2.

vyangliumsft_4-1626915231102.jpeg

 

Best Regards,

Liu Yang

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

Pragati11
Super User
Super User

Hi @gerhigrabun ,

 

The interaction between visuals in Power BI completly depends on the following:

  1. Are you using different datasets to visualise in these 2 visuals?
  2. If the datasets are different, do they have a relationship between them on a common column?
  3. If they have a relationship what the the cardinality of the relationship?

Basically you will need to add details on your data model that you have in Power BI and also the screenshots around what tables are you using to create your both matrix visuals.

 

Thanks,

Pragati

Best Regards,

Pragati Jain

MVP logoDACertAzCert

LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

I am only working from one table, and there are no relationships to other tables. AOI and AOI% are in the same column. 

Hi @gerhigrabun ,

 

I think you mean to say AOI and AOI% are coming from same table. Right?

Is AOI% calculated some how using AOI column?

Can you please add some screenshots of you both matrix visual please in the following scenarios:

  1. first screenshot with both matrix without any selection in the slicer
  2. second screenshot with both matrix with a value selected in the slicer

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

 

Thanks,

Pragati

Best Regards,

Pragati Jain

MVP logoDACertAzCert

LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

First, there will never be a case when there is no selection in the slicer. Either operating income or EBITDA will always be selected.
A screenshot is attached that shows a sample of our table (we cannot provide much more due to sensitivity). There is also a screenshot of the two scenarios for each slicer selection and what the matrix should display as per the data shown in the table screenshot. Of course the other quarters would be filled in, but we are not able to show data for that. The yellow cells show what the slicer would be selected. The green cells show which attribute would be showing based on the yellow slicer selection.MicrosoftTeams-image.pngMicrosoftTeams-image1.png

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors