Reply
sdfoinas
Regular Visitor

Total column to show sum of each year's growth

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:

sdfoinas_0-1701607473947.png

 

 

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

sdfoinas_1-1701607502173.png

 

 

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]))
		)

 

 

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, 

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_1-1701613364839.png

 

 

Jihwan_Kim_0-1701613324405.png

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] ) ) )
    )

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

Hi, 

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_1-1701613364839.png

 

 

Jihwan_Kim_0-1701613324405.png

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] ) ) )
    )

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

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.

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
Super User
Super User

@sdfoinas 

Coud you share the PBI file ?

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

avatar user

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)