Matrix visual with year and company name as a column header

Data Visualizations
1

Hey guys,
I would like to build something like this:

Capture11
Capture11943×389 107 KB

It is suppose to have a filter by year. For example, if i choose 2019, it will show me that year and year -1 and year -2. If i choose 2020 it will show me like the picture is showing.
For this example i would like to make 6 or 7 measures only. I did it with just one year.
My question : how do i make a report like this? Can you give me some ideas?
Thank you all
Best regards
Pedro

2

Hi @pedroccamara,

With just the out of the box visuals the only way to come close, is to combine 2 matrix visuals and start tinkering with the settings to find out what works best - optionally combining other shapes…

I have a simple sales model, so look past the displayed dimensions it’s just to give an idea.

image
image1253×376 50.3 KB

I’ve set a page level filter using the YearOffset so it shows the current and 2 prior years.

Couple of things to keep in mind.

  • for the top matrix, in the Values section enable “show on rows”
  • for both, in the Column Headers section disable “Auto size column width”
  • align column widths manually for both matrix visuals
  • for both, in the general section enable “maintain layer order”
  • for both, disable the top level “Visual Header”
  • finally Group both visuals

That’s my 2 cents. I’m confident others will have more inspiring solutions up their sleeve :wink:
eDNA - combined matix.pbix (433.0 KB)

3 Likes
3

Hello @Melissa
Thanks a lot for your help.
Let me tell you what i did, meanwhile i didn’t get any answer to this post:
This is the P&L Statement, the way Sam taught us to do.

Capture12
Capture121133×632 79.5 KB

I did the first matrix on the left and copy it to the right 2 times and hide the row headers. Now i just have to do another with the differences.

With this “solution” i can pick whatever year i need and month.
Also, i’ve add 3 columns on the dates table, year-0, year-1 and year-2. My main measure for each matrix is filtered by …FILTER( ‘Tab Dates’, ‘Tab Dates’[Year] = ‘Tab Dates’[Year - 0] )). The second will be filtering year-1 and so on.
This solution, instead of doing 7 measures for the subtotals it’s gonna be 21, 7 for each matrix visual.

Also, i’ve never heard about joinning two matrix visuals, as well as the yearoffset, don’t know how it works.
What do you think @Melissa? Should i delete this one and start all over?
Thanks a lot
Pedro

P.S. Forgot to say that those year boxes above are those columns i’ve create on the date table

4

That is totally up to you… Just play around with the sample file - see how far you can take it and choose which method works best for you.

All the best.

1 Like
5

Yes, I will.
Thanks a lot @Melissa