matrix table to show different values for different column value (year)

[RDHR]

Hi guys, I want to create a matrix table like below

For the previous two years, I wanna show the same values(A,B,V,%C)

For the current year, I wanna show the A,%C plus additional two or more different values(E,F..)

Is it possible to achieve this in one matrix table? Thanks!

[Anonymous · Accepted solution]

Hi  @RDHR ,

I created some data:

There are two methods here, but both of them will affect the data structure and presentation to a greater or lesser extent because of the design of Power BI.

Here are the steps you can follow：

First method:

Create measure all put into the Value of the matrix, but the matrix does not automatically hide the value of Value= blank, we need to manually operate:

1. Create measure.

A1 = MAX('Table'[A])

B1 =
IF(
    MAX('Table'[Year])<>2023,MAX('Table'[B]),BLANK())

C1 =
MAX('Table'[%C])

E1 =
IF(
    MAX('Table'[Year])=2023,MAX('Table'[E]),BLANK())

F1 =
IF(
    MAX('Table'[Year])=2023,MAX('Table'[F]),BLANK())

V1 =
IF(
    MAX('Table'[Year])<>2023,MAX('Table'[V]),BLANK())

2. Move the mouse over the column for each blank value, reducing the width of the column to make it invisible to the naked eye

3. Result:

Second method:

Use Power Quer to change the data structure, put the column with values into a column, use Measure to judge, but the data is in one column, the data structure in the same column must be the same.

1. In power query – select [A]—[F] – Transform –Unpivot Columns.

2. Create measure:

Measure =
SWITCH(
    TRUE(),
    MAX('Table (2)'[Year])=2021&&MAX('Table (2)'[Attribute]) in
    {"A","B","V","%C"},MAX('Table (2)'[Value]),
    MAX('Table (2)'[Year])=2022&&MAX('Table (2)'[Attribute]) in
    {"A","B","V","%C"},MAX('Table (2)'[Value]),
    MAX('Table (2)'[Year])=2023&&MAX('Table (2)'[Attribute]) in
    {"A","%C","E","F"},MAX('Table (2)'[Value]),BLANK())

Best Regards,

Liu Yang

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

[RDHR]

Thank you so much, Liu! This is extremely helpful. Much appreciated.