Building a Matrix with Asymmetrical Columns and Rows in Power BI

[migueldfr]

Hello folks,

I am sttrugling with this sheet in this small concept

I would like to create a pivot table and a bar chart upper which describe the table.

Pivot table should contains the values of each quarter in each row and the values for each year in each column. Attached screenshot: 

Quarter	Year	Total Importe Encargo
Q1	2022	1593980
Q1	2023	196626146
Q1	2024	176830989
Q2	2022	159388242
Q2	2023	201556431
Q2	2024	17388848
Q3	2022	17491518
Q3	2023	228064979
Q3	2024	178882189
Q4	2022	166886332
Q4	2023	193844812
Q4	2024	18604966

I would like to add two more columns into the table in power bi. Which will show up the diference between year(today) - year(today) -1 for each quarter and anothe column with the difference in % (variance) 

This is the plot that I created in first instance.

Thank you so much 

I would appreaciatte any help.

[Anonymous]

Hi @migueldfr ,

Thanks for ToddChitt's reply!

And @migueldfr , 

Which will show up the diference between year(today) - year(today) -1 for each quarter and anothe column with the difference in % (variance) 

Are you trying to calculate the difference between two adjacent years in the same quarter?

If yes, please try to use these two DAXs to create calculated columns:

difference = 
VAR _Year = [Year]
VAR _Quarter = [Quarter]
VAR _Current = 
CALCULATE(
    SUM('Table'[Total Importe Encargo]),
    ALL('Table'),
    'Table'[Year] = _Year && 'Table'[Quarter] = _Quarter
)
VAR _Previous = 
CALCULATE(
    SUM('Table'[Total Importe Encargo]),
    ALL('Table'),
    'Table'[Year] = _Year - 1 && 'Table'[Quarter] = _Quarter
)
VAR _MINYear = 
CALCULATE(
    MIN('Table'[Year]),
    ALL('Table')
)
RETURN
IF(
    'Table'[Year] = _MINYear,
    0,
    _Current - _Previous
)

variance = 
VAR _Year = [Year]
VAR _Quarter = [Quarter]
VAR _Currentdifference = [difference]
VAR _Previous = 
CALCULATE(
    SUM('Table'[Total Importe Encargo]),
    ALL('Table'),
    'Table'[Year] = _Year - 1 && 'Table'[Quarter] = _Quarter
)
VAR _MINYear = 
CALCULATE(
    MIN('Table'[Year]),
    ALL('Table')
)
RETURN
IF(
    'Table'[Year] = _MINYear,
    0,
    _Currentdifference / _Previous
)

And the final output is as below:

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

[migueldfr]

Good afternoon everyone.

Following the last example I am doing some new features and seem like I am getting there, but sadly I left something.

I have created a matrix with the Quarter and Years

Also I added a Total of sales, Difference between Quarters (from each year) and Variance (%) 

As a final result, I would like to create a Matrix with Year on the top and Quarter on the Left side...

At the moment, I have this:

Thank you for any help you could bring to me

I would appreaciate

[migueldfr]

Hello @Anonymous 

thank you for you response. 

I guess we are close to the result.
I would like to replicate this table made in excel.

The point here, is depend on what you are using, if you using if you use pivot table you are gonna see it like this:
ROWS = QUARTER

COLUMNS = YEAR

QUARTER	2022	2023	2024	Diferencia	Varianza
Q1	1593980	196626146	176830989
Q2	159388242	17491518	166886332
Q3	201556431	228064979	193844812
Q4	17388848	178882189	18604966

I would appreciate your help 

Thank you in advance

[migueldfr]

I keep working and I found another way to approah.

Building a Matrix with Asymmetrical Columns and Rows in Power BI

This what I am using.
I created a table to do it and aslo a measure.

Valores_A_mostrar = 
    VAR Anioseleccionado = VALUE(SELECTEDVALUE(x_Encabezados_tabla[Orden]))  -- Aseguramos que Anioseleccionado es un valor numérico
    VAR AnioActual = YEAR(TODAY())
    VAR ValorAnioActual = CALCULATE([Total Importe Encargo], FILTER('MasterCalendar', 'MasterCalendar'[Year] = AnioActual))
    VAR ValorAnioAnterior = CALCULATE([Total Importe Encargo], FILTER('MasterCalendar', 'MasterCalendar'[Year] = AnioActual - 1))

    VAR Diferencia = ValorAnioActual - ValorAnioAnterior
    VAR PorcentajeCambio = DIVIDE(Diferencia, ValorAnioAnterior, 0)

    RETURN 
        SWITCH(
            TRUE(),
            Anioseleccionado < 5, [Total Importe Encargo], -- Para los valores menores a 5, muestra el Total Importe Encargo
            Anioseleccionado = 5, Diferencia,  -- Cuando el valor de Orden es 5, muestra la diferencia
            Anioseleccionado = 6, FORMAT(PorcentajeCambio, "0.00%") -- Cuando el valor de Orden es 6, muestra el porcentaje
            
        )

But sadly, this does not work as I expected.

Could anyone give me a hand with this ?

Thanks

[ToddChitt]

Check out Visual Calculations

Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn

Create a new Visual Calculation using the PREVIOUS function, and use AXIS of COLUMNS. Once you have that, you could probably figure out the Variance and Delta Visual Calculations as well.

[migueldfr]

Hello, 

I am not able to select the one you are talkig about 

Even I tried to add in the preview preliminary but nothing comes out.

Thank you