Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have some marks for students and I wanted to find the percentage change of the average of marks from one year to the next. eg For student 113048 (See link to data below) the Current Year Average = 60. CurrentYear - 1 = 73.24. Current Year -2 = 76.69. From currentyear to currentyear - 1 this is a 22.06% fall in average scaled mark. I am trying to do this all in measures with limited success.
The measure below will give me the average of the current year (I have a slicer which I use for the student ids)
The average of the currentyear - 1 is the same measure with Year(now())-1.
Scld Mark Average Current = calculate( AVERAGE(uncRedshift_Studentresults[Scld]), Filter(uncRedshift_Studentresults,uncRedshift_Studentresults[FileYear] = Year(now()) ))
I am then having trouble using a measure to get the percentage change between the two.
Solved! Go to Solution.
@dphillips add following 3 measures, you can always combine all this in one if you want.
Avg Mark = AVERAGE( Sheet1[Scld Mark] ) Avg Mark Prev Year = VAR __prevYear = MAX( Sheet1[FileYear] ) - 1 RETURN CALCULATE( [Avg Mark], Sheet1[FileYear] = __prevYear ) Avg Mark Change % = DIVIDE( [ Avg Mark Prev Year] - [Avg Mark], [Avg Mark] )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@dphillips but it depends on how you want to view your data, if you have year in your visuals then it will take year in filter context of this measure.
For example, use matrix visual, put student on rows, years on columns and then these 3 measures on values, you will see the change of each year from prev year. All depends on the need, you can surely change the measure as per your need.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@dphillips add following 3 measures, you can always combine all this in one if you want.
Avg Mark = AVERAGE( Sheet1[Scld Mark] ) Avg Mark Prev Year = VAR __prevYear = MAX( Sheet1[FileYear] ) - 1 RETURN CALCULATE( [Avg Mark], Sheet1[FileYear] = __prevYear ) Avg Mark Change % = DIVIDE( [ Avg Mark Prev Year] - [Avg Mark], [Avg Mark] )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hello Parry
This was helpful, just want to know how to combine all 3 measures
Moreover I have created 3 average measures for Current year, last Year and Last Year-1 with VAR, but not able to RETURN required results which i want with a single measure
BR//Naresh
Thanks for the info. Just a question about the first line..
Avg Mark = AVERAGE( Sheet1[Scld Mark] )
Won't this give me the average over all years, not just the most current year? Don't I need to filter this in some way to get just the most current year?
@dphillips but it depends on how you want to view your data, if you have year in your visuals then it will take year in filter context of this measure.
For example, use matrix visual, put student on rows, years on columns and then these 3 measures on values, you will see the change of each year from prev year. All depends on the need, you can surely change the measure as per your need.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |