Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sdukes_88
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
v-yuezhe-msft
Employee
Employee

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.

View solution in original post

2 REPLIES 2
v-yuezhe-msft
Employee
Employee

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.
kcantor
Community Champion
Community Champion

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




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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