Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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)
Thanks,
Lydia Zhang
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)
Thanks,
Lydia Zhang
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.
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |