Forum Discussion
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 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.
7 Replies
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.
Hello,
I am not able to select the one you are talkig aboutEven I tried to add in the preview preliminary but nothing comes out.
Thank you
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.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 = QUARTERCOLUMNS = 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
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
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 YearsAlso 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
For the Quarter vs Year matrix with YoY variance columns, the cleanest native approach is using Visual Calculations (available from March 2025 update):
YoY Diff = [Sales] - CALCULATE([Sales], PREVIOUSYEAR(DATES))
YoY % = DIVIDE([YoY Diff], ABS(CALCULATE([Sales], PREVIOUSYEAR(DATES))))Visual Calculations run in the visual context so they respect the expand/collapse of your matrix hierarchy automatically no need to write ALLSELECTED or manage filter context manually.
If your Finance users need to switch which years to compare themselves in the published report (without going back to Desktop), Flexa Tables on AppSource handles this as a built-in button end users select two periods and variance columns appear instantly
