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

Get 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

Reply
Anonymous
Not applicable

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

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 DateTest DateStudent NameTest Score
Aug 7Aug 3Ron95%
Aug 7Aug 3Rudy89%
Aug 7Aug 6James87%
Aug 7Aug 5Ron91%
Aug 14Aug 11Rudy87%
Aug 21Aug 18James93%
Aug 14Aug 12Ron80%
Aug 14Aug 12Rudy92%
Aug 21Aug 17Ron85%

 

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 NameAug 7 Average Test ScoreAug 21 Average Test ScoreDifference
Ron93%85%-8%

James

87%93%6%
Rudy89%blankblank

 

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,

1 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

 

 


Capture1.JPGCapture2.JPG

View solution in original post

26 REPLIES 26
Crystal_YW
Helper I
Helper I

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 columnCapture1.JPGCapture2.JPGCapture3.JPG

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
Not applicable

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

Anonymous
Not applicable

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
Not applicable

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

@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
Not applicable

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

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

 

 


Capture1.JPGCapture2.JPG

Anonymous
Not applicable

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
Not applicable

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
Not applicable

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
Helper I
Helper I

Hello, is this what you want as follows:

but I made it simple without calendar, maybe you could tailormaid the data model by your selfCapture1.JPGCapture2.JPG

Anonymous
Not applicable

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?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

@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

 

 

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

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 

 

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.