Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 t...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  Anonymous ,

    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's avatar
Pragati11
Super User
5 years ago

Hi Anonymous ,

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    I am only working from one table, and there are no relationships to other tables. AOI and AOI% are in the same column. 
    • Pragati11's avatar
      Pragati11
      Super User
      5 years ago

      Hi Anonymous ,

       

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago
        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.