Matrix Help

[jawilson808]

Hi there!

Hoping you can help me find a solution to a couple of issues I am having with a matrix visual.
What I am trying to accomplish:

1. Give my matrix custom headers

2. By default, current year and variance should always show

3. Allow user to select a diffent period to compare ( Last Year Column below)

4. Calculate the variance between current year and the selected value(Last year column below)

As an example:

Period     Current Year                Last Year (should change)                 Variance

Date        QTY   Revenue            QTY   Revenue                                      QTY Revenue

Jan           1       105                    2       110                                               -1    -5

Mar          2       107                    2       107                                                0     0

....  

To accomplish step 1, I created a blank table with all of my time periods including current year and variance.

Table looks as such:

Period

Current Year

Last Year

Forecast

2 Years Ago

Variance

My measures were calculated by a variable dax that looks as such:

QTY=

Var CurrentYear=sum(currentrev)
Var LastYear=Sum(lastyearrev)

Var 2Yearsago=sum(twoyearsrev)

Return
if(selectedvalue(column)="Current Year",CurrentYear,if(selectedvalue....

I have successfully created the table, but not able to accomplish 2-4. 

Any help would be greatly appreciated

[Jihwan_Kim]

Hi, @jawilson808 

If it is OK with you, please share your sample data file, then I can try to come up with more accurate measures to solve your problem.

In this case, I can try to use a time intelligent function like, DATEADD.

Thank you.

Jihwan Kim

If this post helps, then please consider accept it as the solution to help the other members find it more quickly.

[jawilson808]

Hi @Jihwan_Kim ,
Thank you for getting back to me. You can access the sample data here https://drive.google.com/file/d/1d5Qnk1UccKTypGw6wvWXSMxqMghceQv2/view?usp=sharing

Thank you.

[Jihwan_Kim · Accepted solution]

Hi, @jawilson808 

Thank you for sharing your sample data.

I have tried, but could not come up with a nicer solution than what I have done in the link below.

The visualization is not a matrix, but it is a table visualization.

Please check.

https://www.dropbox.com/s/2rr7efzh464j729/Sample%20pbix.pbix?dl=0 

[jawilson808]

Thank you @Jihwan_Kim for your assistance. I actually used this setup in a previous report but its not as clean when you have muliple measures that you are attempting to "group" and use the same naming convention. I think the best way I can train my users would to use some type of color coding and the groupings as you demonstrated. I greatly appreciate you looking into this.