Linking two matrix with different data

[gerhigrabun]

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%? 

[Anonymous · Accepted solution]

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：

Matrix 1:

Matrix 2:

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.

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

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]

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

[gerhigrabun]

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

[Pragati11]

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

[gerhigrabun]

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.