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 screenshot of data. 
Year scenario-2 is a duplicate column of year scenario. If I select something in filter Year scenario then Amount1(which is a measure based on year scenario) should change and If I select something from Year scenario-2 then Amount2(which is a measure based on year scenario-2) should change. So that I can compare amount with different scenarios(For example- 2015 ACT vs 2018 BGT, 2016 FQ1 vs 2017 ACT etc). 

Sample data :

Account AmountYear Scenario
XXXXXXX5000002015 BGT
AAAAAA4000002015 BGT
AABBBBB1000002015 ACT
ZZZZZZZZ7305012016 ACT
AABBBBB6463242016 FQ1
AABBBBB8233832016 BGT
XXXXXXX3087112016 BGT
ZZZZZZZZ9634622016 FQ3
AABBBBB5931402016 FQ3
XXXXXXX4095432017 BGT
ZZZZZZZZ3405172017 BGT
XXXXXXX3937782017 ACT
AAAAAA8891712017 FQ1
ZZZZZZZZ9256652017 FQ1
AAAAAA1365492017 FQ3
AABBBBB6507492017 FQ3
XXXXXXX5230942018 BGT
ZZZZZZZZ5548252018 BGT
   

Any suggestion is appreciated.

Thanks,

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

8 Replies

  • 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