Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Compare values in matrix visual with different filters

Hello Everyone, I have a dataset with 3 columns Account, Amount and Year Scenario. I want to create a matrix visual in which I can compare different scenarios with each other.  I have attached a sc...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi Anonymous,

     

    Based on my test with your shared pbix file, you may need to take two more steps to make it work.

     

    1. Remove all relationships among the three tables.

     

     

    2. Use "Year Scenario" column from Date1, and "Year Scenario-2" column from Date2 as Slicers, instead of using them from Test_data table.

     

     

    Here is the modified pbix file for your reference. :smileyhappy:

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi Anonymous,

 

If I understand you correctly, you should be able to firstly add to two individual calendar tables in your model which contains the column 'Year scenario' and 'Year scenario-2' separate.

 

Then use the formulas below to create two measures to calculate Amount for the selected 'Year scenario' and 'Year scenario-2' in your scenario. :smileyhappy:

Amount1 =
CALCULATE (
    SUM ( 'Table1'[Amount] ),
    FILTER (
        'Table1',
        CONTAINS ( Date1, Date1[Year Scenario], 'Table1'[Year Scenario] )
    )
)

Amount2 =
CALCULATE (
    SUM ( 'Table1'[Amount] ),
    FILTER (
        'Table1',
        CONTAINS ( Date2, Date2[Year Scenario-2], 'Table1'[Year Scenario] )
    )
)

Note: You'll need replace the bold with your real table names.

 

Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hello v-ljerr-msft

     

    Thank you for the reply. 

    I am still facing the same issue. Let's say I select Year Scenario 2015 ACT from filter Year Scenario then I am getting the same amount in Amount1 and Amount2. And when I select Year Scenario 2015 ACT from filter Year Scenario and 2017 BGT from Year scenario-2, I am getting blank values in both Amount1 and Amount2. 
    What I want is to compare Amount for different Year scenarios (For example - 2015 ACT vs 2017 BGT).

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      Hi Anonymous,

       

      Could you share a dummy pbix file which can reproduce the issue, so that we can further assist on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. :smileyhappy:

       

      Regards

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      Hi Anonymous,

       

      Based on my test with your shared pbix file, you may need to take two more steps to make it work.

       

      1. Remove all relationships among the three tables.

       

       

      2. Use "Year Scenario" column from Date1, and "Year Scenario-2" column from Date2 as Slicers, instead of using them from Test_data table.

       

       

      Here is the modified pbix file for your reference. :smileyhappy:

       

      Regards

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi v-ljerr-msft

         

        Thank you for the help. 
        After removing the relationship, it worked for me.

         

        Thanks