cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 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,

1 ACCEPTED SOLUTION
Helper I

@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())
"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

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

Helper I

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

Helper I

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

Helper I

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.

Anonymous
Not applicable

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

Helper I

@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())
"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
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?

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 self

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

Helper I

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

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper I

@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

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

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.

Super User

@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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.