Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Create Matrix column to show % difference between years

I have a matrix in power BI Desktop like the following one (real data deleted due to confidentiality reasons): 


To build this table I used the following DAX: 

Valores P&L Cuenta Explotación =
VAR _conceptoNivel2 = max('P&G - Jerarquía'[orden nivel 2])
VAR _conceptoNivel1 = max('P&G - Jerarquía'[orden nivel 1])
var _conceptoDesc = max('P&G - Jerarquía'[orden descripción])
VAR _calc =
SWITCH(
    TRUE(),
    _conceptoNivel2=1, [Gastos Totales P&G],
    _conceptoNivel2=2, [Ingresos Totales P&G],
    _conceptoNivel2=3 && _conceptoNivel1= 11, [EBITDA],
    _conceptoNivel2=3 && _conceptoNivel1= 12, [Amortizaciones],
    _conceptoNivel2=3 && _conceptoNivel1= 13, [Beneficio Antes Impuestos]
)
 
RETURN IF(ISBLANK(_calc),0,_calc)

And for the columns I use "'DIM Calendar'[Year]".
 
Now I would need to ad a column in between each year that shows the % change vs the previous year. The new columns should look something like this for every row:  (ideally just for the Ventas (green) grouping, but if its for all of the rows its fine)
 
2024 | % vs Previous year | 2023 | % vs Previous year | 2022 |
------------------------------------------------------------------
1100 | +10%                     | 1000 | +20%                     | 800   | 
 
I need that when a grouping is closed or open it shows the % for the value showed in that specific groping. If I close the Ventas group, the % should be for the total Ventas value. If it is open it should be for every subgroup. 
 
 
Is there a way to do this? Thanks a lot! 
General Comment
Need Help
Show and Tell
  • Samarth_18's avatar
    Samarth_18
    2 years ago

    Hi Anonymous ,

     

    You could try below measure:-

    CurrentYearValue = 
CALCULATE(
    [Valores P&L Cuenta Explotación],
    FILTER(
        ALL('DIM Calendar'),
        'DIM Calendar'[Year] = SELECTEDVALUE(Years[Value])
    )
)

PreviousYearValue = 
CALCULATE(
    [Valores P&L Cuenta Explotación],
    FILTER(
        ALL('DIM Calendar'),
        'DIM Calendar'[Year] = SELECTEDVALUE(Years[Value]) - 1
    )
)

PercentChange = 
IF(
    ISBLANK([PreviousYearValue]),
    BLANK(),
    DIVIDE([CurrentYearValue] - [PreviousYearValue], [PreviousYearValue], 0)
)

1 Reply

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    2 years ago

    Hi Anonymous ,

     

    You could try below measure:-

    CurrentYearValue = 
CALCULATE(
    [Valores P&L Cuenta Explotación],
    FILTER(
        ALL('DIM Calendar'),
        'DIM Calendar'[Year] = SELECTEDVALUE(Years[Value])
    )
)

PreviousYearValue = 
CALCULATE(
    [Valores P&L Cuenta Explotación],
    FILTER(
        ALL('DIM Calendar'),
        'DIM Calendar'[Year] = SELECTEDVALUE(Years[Value]) - 1
    )
)

PercentChange = 
IF(
    ISBLANK([PreviousYearValue]),
    BLANK(),
    DIVIDE([CurrentYearValue] - [PreviousYearValue], [PreviousYearValue], 0)
)