Forum Discussion

migueldfr's avatar
migueldfr
Helper IV
1 year ago

Building a Matrix with Asymmetrical Columns and Rows in Power BI

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 val...
Need Help
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
migueldfr
Helper IV
1 year ago

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

 

QUARTER202220232024DiferenciaVarianza
Q11593980196626146176830989  
Q215938824217491518166886332  
Q3201556431228064979193844812  
Q41738884817888218918604966  

I would appreciate your help 

Thank you in advance



  • migueldfr's avatar
    migueldfr
    Helper IV
    1 year ago

    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