Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I have a table of students with weekly test scores. I need to be able to get the difference of the quiz scores in a matrix based on a filter.
Sample table:
Weekending Date | Test Date | Student Name | Test Score |
Aug 7 | Aug 3 | Ron | 95% |
Aug 7 | Aug 3 | Rudy | 89% |
Aug 7 | Aug 6 | James | 87% |
Aug 7 | Aug 5 | Ron | 91% |
Aug 14 | Aug 11 | Rudy | 87% |
Aug 21 | Aug 18 | James | 93% |
Aug 14 | Aug 12 | Ron | 80% |
Aug 14 | Aug 12 | Rudy | 92% |
Aug 21 | Aug 17 | Ron | 85% |
Selected filter weekending dates (Aug 7 & Aug 21) - selection may vary depending on preferred weekending dates to compare.
Matrix should show difference of average weekly scores of students. If one of the weekly score average is blank (meaning the student did not take any test on that week), the difference should be blank)
Student Name | Aug 7 Average Test Score | Aug 21 Average Test Score | Difference |
Ron | 93% | 85% | -8% |
James | 87% | 93% | 6% |
Rudy | 89% | blank | blank |
I have tried several solutions from this forum but I cannot get it to work. I am getting the wrong difference. I really appreciate your help. In addition, I need the difference column to have conditional formatting to highlight the positive values as green and the negative as red.
Cheers and stay safe,
Solved! Go to Solution.
@Anonymous
finally, in my report, I've realized that using one measure to realize your presentation results
I have to use my own report 'cause i can't open your shared file,
try this measure2, let me know if it suits for you or not
Hi:
I've fixed the issues and I've add some more test score to double check my code and it's accurate. only one variable was added and use divide in the new column
by the way, for the % format , just click the measure and change the formatting on the ribbon to %, easy to resolve
the code is little bit verbose and hope I could make it shorter later
Thank you @Crystal_YW . % is now okay, there was just a delay earlier when I hit the % formatting however I am still getting inaccurate results when I applied your code. a difference of -1.11% is giving me -0.08% and -1.47% is giving me -0.15% and -19.90% is giving me -3.06% 😞
@Anonymous hi, figures were accurate in my report. maybe you could upload your pbix file, let me check what is wrong with the formula
I have a question to check if I understood your code correctly, @Crystal_YW
You only used the maxweek to count rows and then divided the difference of max and min week averages by maxweektimes. How about if the min week countrows is not the same as the maxweek count rows. Will this cause the inaccurate result?
@Anonymous no, this isn't the reason.
Funny enough, result in my report is accurate. I am wondering if something wrong with your own modification.
If your data is sentive, you can use sample data in your pbix and upload it. I would check it, otherwise hard to dectect the root cause.
@Anonymous please use gooledrive to upload the files
the below paragram from the article "How to provide sample data in the Power BI Forum"
Especially when you have a problem with a DAX statement or the data model, it would be best to provide a pbix-file containing the sample data. You might have seen posts here in the forum where files are directly attached and have searched for the button to do exatly that. But unless you're a Microsoft employee or a super user this feature will not be available for you. Instead you have to upload your file to a cloud storage (like OneDrive, Dropbox, Google Drive or Wetransfer for example) and paste the link to that storage location in the post.
Thanks for that information. Here is the link to the file https://drive.google.com/file/d/1TwzcINfL8BlsqdUYt30fuJm84cMBwwp9/view?usp=sharing
@Anonymous
finally, in my report, I've realized that using one measure to realize your presentation results
I have to use my own report 'cause i can't open your shared file,
try this measure2, let me know if it suits for you or not
Sorry I got sick and only got the chance to work on this today. I just want to say a big thank you to @Crystal_YW for not giving up on me.
I'll check what I can provide. Meanwhile, is it okay for you to send me your working pbix file so I can check how you set it up from the raw data?
I am sorry @Crystal_YW we have some sensitive information in our data. I really appreciate your effort. Your code looks great but is it possible to collaborate with @Greg_Deckler to resolve the blank issue with his code?
Hello, is this what you want as follows:
but I made it simple without calendar, maybe you could tailormaid the data model by your self
Thank you very much! Your code resolved the blank results but the difference does not seem to be accurate and it is not converting to %.
I very much appreciate your effort. 🙂
hello, I know where the issue is in my code. As I use sumx to aggregate the average result on max weekend date choose, whenever there is more than one test score, the average result would be double counted. I would fix it
@Crystal_YW - Not sure I fully understand, use AVERAGEX instead of SUMX?
@Greg_Deckler still use averagex to calculate the average score. But I use sumx to get the final difference, which result in double count. Now it fixed
@Anonymous , You can create a date or week calendar and Date calendar and Have week Rank there and then you can use that to find Avg of this week vs last week.
Date or week Table to have these columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date] & " to "& [Week End date]
Weekday = WEEKDAY([Date],2)
WeekDay Name = FORMAT([Date],"ddd")
Example
This Week = CALCULATE(sum('Table'[Test Score]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Test Score]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
diff = [This Week]-[Last Week]
diff % = divide([This Week]-[Last Week],[Last Week])
refer for more details
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
Thank you for your solution @amitchandak but I couldn't get the Date table to show the correct week start date and week end date. week starts on a saturday and ends on a friday.
@Anonymous , I have a calendar that contains week start and week end for any week day
All possible 7 combinations are covered
Find file at https://www.dropbox.com/s/u8g7u1ra61raiuh/Every_Weekday_cal.pbix?dl=0
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
20 | |
19 | |
13 | |
12 |
User | Count |
---|---|
41 | |
27 | |
23 | |
22 | |
22 |