Compare values in matrix visual with different filters

[Anonymous]

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	Amount	Year Scenario
XXXXXXX	500000	2015 BGT
AAAAAA	400000	2015 BGT
AABBBBB	100000	2015 ACT
ZZZZZZZZ	730501	2016 ACT
AABBBBB	646324	2016 FQ1
AABBBBB	823383	2016 BGT
XXXXXXX	308711	2016 BGT
ZZZZZZZZ	963462	2016 FQ3
AABBBBB	593140	2016 FQ3
XXXXXXX	409543	2017 BGT
ZZZZZZZZ	340517	2017 BGT
XXXXXXX	393778	2017 ACT
AAAAAA	889171	2017 FQ1
ZZZZZZZZ	925665	2017 FQ1
AAAAAA	136549	2017 FQ3
AABBBBB	650749	2017 FQ3
XXXXXXX	523094	2018 BGT
ZZZZZZZZ	554825	2018 BGT

Any suggestion is appreciated.

Thanks,

[v-ljerr-msft]

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. 

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]

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 · Accepted solution]

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. 

Regards

[Anonymous]

Hi @v-ljerr-msft

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

Thanks

[Anonymous]

Hello @v-ljerr-msft, 

Is there a way to calculate this within the same table (test_data). I need to use year scenario for other visual also and there should be an interaction between all the visualization.

I created a new table Measure Dimensions with year scenario(Measure). I created measures for different scenarios and using them in the switch function. 

Measure Selection =
SWITCH( TRUE(),
VALUES('Measure Dimensions'[Measure]) = "2015 ACT", [2015 ACT],
VALUES('Measure Dimensions'[Measure]) = "2015 BGT", [2015 BGT],
VALUES('Measure Dimensions'[Measure]) = "2015 FQ1", [2015 FQ1],
VALUES('Measure Dimensions'[Measure]) = "2015 FQ3", [2015 FQ3],
VALUES('Measure Dimensions'[Measure]) = "2016 ACT", [2016 ACT],
VALUES('Measure Dimensions'[Measure]) = "2016 BGT", [2016 BGT],
VALUES('Measure Dimensions'[Measure]) = "2016 FQ1", [2016 FQ1],
VALUES('Measure Dimensions'[Measure]) = "2016 FQ3", [2016 FQ3],
VALUES('Measure Dimensions'[Measure]) = "2017 ACT", [2017 ACT],
VALUES('Measure Dimensions'[Measure]) = "2017 BGT", [2017 BGT],
VALUES('Measure Dimensions'[Measure]) = "2017 FQ1", [2017 FQ1],
VALUES('Measure Dimensions'[Measure]) = "2017 FQ2", [2017 FQ2],
VALUES('Measure Dimensions'[Measure]) = "2017 FQ3", [2017 FQ3],
VALUES('Measure Dimensions'[Measure]) = "2018 BGT", [2018 BGT])

Measure Selection2 =
SWITCH( TRUE(),
VALUES('Measure Dimensions'[Measure]) = "2015 ACT", [2015 BGT],
VALUES('Measure Dimensions'[Measure]) = "2015 BGT", [2016 BGT],
VALUES('Measure Dimensions'[Measure]) = "2015 FQ1", [2016 FQ1],
VALUES('Measure Dimensions'[Measure]) = "2015 FQ3", [2016 FQ3],
VALUES('Measure Dimensions'[Measure]) = "2016 ACT", [2016 ACT],
VALUES('Measure Dimensions'[Measure]) = "2016 BGT", [2015 FQ3],
VALUES('Measure Dimensions'[Measure]) = "2016 FQ1", [2017 FQ1],
VALUES('Measure Dimensions'[Measure]) = "2016 FQ3", [2015 FQ3],
VALUES('Measure Dimensions'[Measure]) = "2017 ACT", [2016 ACT],
VALUES('Measure Dimensions'[Measure]) = "2017 BGT", [2016 ACT],
VALUES('Measure Dimensions'[Measure]) = "2017 FQ1", [2018 BGT],
VALUES('Measure Dimensions'[Measure]) = "2017 FQ2", [2017 BGT],
VALUES('Measure Dimensions'[Measure]) = "2017 FQ3", [2017 BGT],
VALUES('Measure Dimensions'[Measure]) = "2018 BGT", [2017 FQ3])

This is working if there is no relation between Test_data & Measure Dimensions.
I want to use year scenario as a filter and all the visualization should change based on filter selection.

Any suggestions are appreciated.

Thanks

[Anonymous]

Hi, did you ever find a solution?

[v-ljerr-msft]

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. 

Regards

[Anonymous]

Hi @v-ljerr-msft

Here is the link to dummy pbix and data file. 

https://lpl-my.sharepoint.com/:f:/g/personal/prakumar_clasp-infra_com/EmmpgeCXT7tDkOC5T7OQFJQByP25yU...

Looking forward to your response.

Thanks.