cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sdukes_88 Frequent Visitor
Frequent Visitor

Creating a % change from one value

Hi,

 

I've got two years' worth of data which is split in my matrix (column) by Academic Year.

 

So like this:

 

                           2015/16                                   2016/17

A                          100                                            120

B                            80                                               90

C                            78                                               77

 

The value is called Funding.

 

Is it possible to create a % change when it is only one value? (i.e. I've read that you can do this if two seperate columns e.g. Funding15 & Funding16).

 

Any help appreciated!

 

Thanks

Simon

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Creating a % change from one value

Hi @sdukes_88,

You can create the following columns in your table. Replace table and columns with your owns in the formulas below.

Difference = IF(Table7[Type]=LOOKUPVALUE(Table7[Type],Table7[Index],Table7[Index]-1),Table7[Funding]-LOOKUPVALUE(Table7[Funding],Table7[Index],Table7[Index]-1),0)

 

% change = DIVIDE(Table7[Difference],LOOKUPVALUE(Table7[Funding],Table7[Index],Table7[Index]-1),0)

1.PNG2.PNG

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
kcantor Super Contributor
Super Contributor

Re: Creating a % change from one value

@sdukes_88

It can be simple but you will need a date table and to create a total for current scores and last year scores. You always want to build on measures, not on actual columns if possible. So, if your data is coming from your Fact Table using the column of funding:

Current Funding = SUM('Fact'[Funding])

Last Year = CALCULATE([Current Funding], DATEADD(DateTable[DateKey], -1, year))

Change = [Current Funding]-[Last Year]

%Change = DIVIDE([Change], [Last year])

You would then filter to the current year and put your Grade in the rows of the matrix with the measures in the columns. In fact, you would only need to add the [% Change] measure and not show the other calculations unless you wanted to.

On point, This is assuming a standard date table. You might need to create a custom date table with academic periods similar to a fiscal calendar since your academic year crosses the official end of the year mark.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Moderator v-yuezhe-msft
Moderator

Re: Creating a % change from one value

Hi @sdukes_88,

You can create the following columns in your table. Replace table and columns with your owns in the formulas below.

Difference = IF(Table7[Type]=LOOKUPVALUE(Table7[Type],Table7[Index],Table7[Index]-1),Table7[Funding]-LOOKUPVALUE(Table7[Funding],Table7[Index],Table7[Index]-1),0)

 

% change = DIVIDE(Table7[Difference],LOOKUPVALUE(Table7[Funding],Table7[Index],Table7[Index]-1),0)

1.PNG2.PNG

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 163 members 2,081 guests
Please welcome our newest community members: