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

  • migueldfr's avatar
    migueldfr
    Helper IV
    1 year ago

    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