Rate of change from previous column

[dphillips]

Is there a simple way of getting the change in value from one column to the next?

I have a table of data which is a count of the number of HSCBands

I created a measure as I wanted to see the percentage of Band 6 and E4s compared to the total from year to year.

% of Band 6 and E4 = (calculate(count(uNCStudentHSCResults[HSCBand]),filter(uNCStudentHSCResults,uNCStudentHSCResults[HSCBand] = "6" || uNCStudentHSCResults[HSCBand] = "E4"))/ COUNT(uNCStudentHSCResults[HSCBand]))

When I put this in a column graph I get.

Which is exactly what I want.

Now I also want a graph that show the change from year to year.

Something that looks like this.'

Note: FileYear is NOT a date field - it is purely an integer.

Can someone help so I can get a simple measure of change from one column to the next?

[v-huizhn-msft · Accepted solution]

Hi @dphillips,

Based on your first bar chart, you want to get the change 14.99-21.07=-6.08% for 2010, 19.37%-14.99%=4.38% for 2011 and so on. If my understanding is right, please create a measure using the formula below.

Previous_year_percentage =
VAR current_year =
    MAX ( uNCStudentHSCResults[Fileyear] )
RETURN
    DIVIDE (
        CALCULATE (
            COUNT ( uNCStudentHSCResults[HSCBand] ),
            FILTER (
                ALL ( uNCStudentHSCResults ),
                ( uNCStudentHSCResults[HSCBand] = "6"
                    || uNCStudentHSCResults[HSCBand] = "E4" )
                    && uNCStudentHSCResults[Fileyear]
                    = current_year - 1
            )
        ),
        CALCULATE (
            COUNT ( uNCStudentHSCResults[HSCBand] ),
            FILTER (
                ALL ( uNCStudentHSCResults ),
                uNCStudentHSCResults[Fileyear]
                    = current_year - 1
            )
        )
    )

Then you can get the Rate of change using the formula, create a bar chart, put the Fileyear as X-axis, the measure as value, you will get expected result.

change of rate = 
VAR current_year =
    MAX ( uNCStudentHSCResults[Fileyear] )
RETURN IF(ISBLANK(uNCStudentHSCResults[Previous_year_percentage]),
	0,
	uNCStudentHSCResults[% of Band 6 and E4]-uNCStudentHSCResults[Previous_year_percentage]   
)

I test it using the following sample table(similar with you) and get expected result. You can download to review the attachment for more details.

sample table
expected result
Best Regards,
Angelia

[dphillips]

That is exactly what I was after! Thanks for your help.