Percentage change measure by row

[Linda200]

I am pretty new to creating measures and hope someone can help wih this.

I am wanting to calulate % change between two columns (2 different years) but would also like the results to be further broken down by categories (i.e. different markets) that are contained in rows. The measure I have just creates the total % change. This is the measure I am using:

Expenditure growth 2016 to 2017 =

VAR __BASELINE_VALUE = SUM('Volume and Exp 2016'[Expenditure])

VAR __VALUE_TO_COMPARE = SUM('Volume and Exp 2017'[Expenditure])

RETURN

    IF(

        NOT ISBLANK(__VALUE_TO_COMPARE),

        DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)

    )

Any help is appreciated!

[Ashish_Mathur · Accepted solution]

Hi,

Ideally you should append the two datasets (Give a name to this appended dataset as Data).  Then create a Calendar Table and build a relationship from the Date column of the appended dataset to the Date column of the Calendar Table.  In the Calendar Table, extract Year and Month by using the following calculated column formulas: Year = Year(Calendar[Date]) and Month = FORMAT(Calendar[Date],"mmmm").  In your visual, drag Yea from the Calendar Table (along with other fieldd such as Categories.  Write these measures:

Total Expenditure = SUM(Data[Expenditure])

Total Expenditure LY = CALCULATE([Total Expenditure],PREVIOUSYEAR(Calendar[Date]))

Growth in expenditure (%) = IFERROR([Total Expenditure]/[Total Expenditure LY],BLANK())

Hope this helps.

[Linda200]

Thanks. This helps!

[Ashish_Mathur]

You are welcome.