Get difference between 2 columns of power bi matrix from selected filters

[Anonymous]

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,

[Crystal_YW]

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

[Anonymous]

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% 😞

[Crystal_YW]

@Anonymous hi, figures were accurate in my report. maybe you could upload your pbix file, let me check what is wrong with the formula

[Anonymous]

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?

[Crystal_YW]

@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]

@Crystal_YW  couldn't see an option to attach my pbix file. 

[Crystal_YW]

@Anonymous  please use gooledrive to upload the files

the below paragram from the article "How to provide sample data in the Power BI Forum"

1) Uploading files

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.

[Anonymous]

Thanks for that information. Here is the link to the file https://drive.google.com/file/d/1TwzcINfL8BlsqdUYt30fuJm84cMBwwp9/view?usp=sharing

[Crystal_YW · Accepted 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

Measure 2 =

var t1=SUMMARIZE('Sheet4','Sheet4'[Student Name],'Sheet4'[Weekending Date])

var MaxWeek=CALCULATE(max('Sheet4'[Weekending Date]),ALLSELECTED())

var MinWeek=CALCULATE(min('Sheet4'[Weekending Date]),ALLSELECTED())

var t2=ADDCOLUMNS(t1,

                  "AvgScoreDiff",

                  var SN='Sheet4'[Student Name]

                  var SD='Sheet4'[Weekending Date]

                  var MaxWeekAvgScore=CALCULATE(AVERAGE('Sheet4'[Test Score]),

                           filter('Sheet4','Sheet4'[Student Name]=SN&&'Sheet4'[Weekending Date]=MaxWeek)

                                  )

                  var MinWeekAvgScore=CALCULATE(AVERAGE('Sheet4'[Test Score]),

filter('Sheet4','Sheet4'[Student Name]=SN&&'Sheet4'[Weekending Date]=MinWeek)

                                  )

                   return

                  if(not ISBLANK(MaxWeekAvgScore)&&not ISBLANK(MinWeekAvgScore),MaxWeekAvgScore-MinWeekAvgScore),

                  "Avg",

                  var SN='Sheet4'[Student Name]

                  var SD='Sheet4'[Weekending Date]

                  var score=CALCULATE(AVERAGE('Sheet4'[Test Score]), filter('Sheet4','Sheet4'[Student Name]=SN&&'Sheet4'[Weekending Date]=Sd))

                  return

                  score

        )

var result=SWITCH(true(),

            ISINSCOPE('Sheet4'[Weekending Date]),maxx(t2,[Avg]),maxx(t2,[AvgScoreDiff]))

var resultfinal=SWITCH(true(),

         not ISBLANK(result),result,"blank")

return

resultfinal

[Anonymous]

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. 

[Anonymous]

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?

[Anonymous]

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?

[Crystal_YW]

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

[Anonymous]

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. 🙂

[Crystal_YW]

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

[Greg_Deckler]

@Crystal_YW - Not sure I fully understand, use AVERAGEX instead of SUMX?

[Crystal_YW]

@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

[amitchandak]

@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

[Anonymous]

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.

[amitchandak]

@Anonymous , I have a calendar that contains week start and week end for any week day 

https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

All possible 7 combinations are covered

Find file at https://www.dropbox.com/s/u8g7u1ra61raiuh/Every_Weekday_cal.pbix?dl=0