Total column to show sum of each year's growth

[sdfoinas]

Hi all,

I have a table where I have year over year growth, and I would like the total column to show the absolute sum of each year's growth.

I have tried with a Summarize formula, and it works well when all years are selected:

However if I filter the years, it no longer works.

I have tried for days to solve it and I am out of ideas. Any help is much appreciated.

This is in excel, here is a link to a sample file with explanation and expected results

The formula I have used is:

SUMX(
		SUMMARIZE(
			Table1,
			Table1[Year],
			"CY Val",[Values CY],
			"PY Val", [Values PY]),
		ABS(DIVIDE([CY Val]-[PY Val],[PY Val]))
		)

[Jihwan_Kim · Accepted solution]

Hi, 

Please check the below picture and the attached pbix file.

OFFSET function (DAX) - DAX | Microsoft Learn

CY value: = 
SUM( Data[Value] )

Growth % = 
VAR _prev =
    CALCULATE (
        [CY value:],
        OFFSET ( -1, ALL ( 'Year'[Year] ), ORDERBY ( 'Year'[Year], ASC ) )
    )
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE ( Data, 'Product'[Product], 'KPI'[KPI], 'Year'[Year] ),
        "@cy", [CY value:],
        "@prev",
            CALCULATE (
                [CY value:],
                OFFSET ( -1, ALL ( 'Year'[Year] ), ORDERBY ( 'Year'[Year], ASC ) )
            )
    )
RETURN
    IF (
        HASONEVALUE ( 'Year'[Year] )
            && NOT ISBLANK ( [CY value:] ) && NOT ISBLANK ( _prev ),
        [CY value:] - _prev,
        SUMX ( _t, ABS ( DIVIDE ( [@cy] - [@prev], [@prev] ) ) )
    )

[sdfoinas]

Many thanks! This is definitely the answer for Power BI.

Is there a way to achieve this with only the functions available in PowerPivot's DAX? For instance OFFSET isn't available.

[sdfoinas]

For anyone else with a similar problem, the answer provided by @Jihwan_Kim works for PowerBI. If you need to do this in Power Pivot (excel) like I did, (where the PowerBI solution can't be used) I was able to achieve this by adding a calculated column in the data model for the YoY Growth and referencing that. This requires that the source table has the same granularity that you want to use in the report, in this case yearly data, but is a possible workaround.

[Fowmy]

@sdfoinas 

Coud you share the PBI file ?