topic Re: Get difference between 2 columns of power bi matrix from selected filters in DAX Commands and Tips

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

Anonymous — Sun, 06 Sep 2020 02:34:01 GMT

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,

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

Greg_Deckler — Sun, 06 Sep 2020 02:45:41 GMT

Anonymous - So, I did it like this so that your total column displays the difference between the two scores:

Measure 5 = VAR __Average = AVERAGE('Table (15)'[Test Score]) RETURN IF(HASONEVALUE('Table (15)'[Weekending Date]),__Average, VAR __Min = MIN([Weekending Date]) VAR __Max = MAX([Weekending Date]) RETURN AVERAGEX(FILTER('Table (15)',[Weekending Date]=__Min),[Test Score]) - AVERAGEX(FILTER('Table (15)',[Weekending Date]=__Max),[Test Score]))

PBIX is attached. Table (15), Page (15), Measure 5

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

amitchandak — Sun, 06 Sep 2020 05:40:59 GMT

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

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

Anonymous — Mon, 07 Sep 2020 01:03:59 GMT

Hi,

I do not know why my reply yesterday did not post but here is what happened when I tried Greg_Deckler .

Using it in my total does not display accurate values but using it in a different matrix with student name and difference column works. However, I still have 2 issues:

Negative values are not showing negative. if 2nd week is lower it should show a decrease or negative difference. (I got this to show negative by putting the max before the min (max - min)
If either 1st or 2nd week is blank, the difference still shows a value instead of a blank. (this remains unresolved. I tried an if formula but it did nothing)

Thank you for your speedy and awesome response.

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

Anonymous — Mon, 07 Sep 2020 00:28:10 GMT

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.

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

amitchandak — Mon, 07 Sep 2020 01:07:14 GMT

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

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

Greg_Deckler — Mon, 07 Sep 2020 01:54:27 GMT

Anonymous - Funny, I was actually trying to fix the blanks issue and I cannot currently find a way to do it. I tried like this

Measure 5 = VAR __Average = AVERAGE('Table (15)'[Test Score]) RETURN IF(HASONEVALUE('Table (15)'[Weekending Date]),__Average, VAR __Min = MIN([Weekending Date]) VAR __Max = MAX([Weekending Date]) VAR __AverageMin = AVERAGEX(FILTER('Table (15)',[Weekending Date]=__Min),[Test Score]) VAR __AverageMax = AVERAGEX(FILTER('Table (15)',[Weekending Date]=__Max),[Test Score]) RETURN //__Min & "-" & __Max IF(ISBLANK(__AverageMin) || ISBLANK(__AverageMax),BLANK(), __Max ) )

Still looking at it.

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

Crystal_YW — Mon, 07 Sep 2020 07:23:12 GMT

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

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

Anonymous — Mon, 07 Sep 2020 13:51:51 GMT

Appreciate you looking into this. Will wait for your update. 🙂

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

Anonymous — Mon, 07 Sep 2020 13:52:46 GMT

Will try this out and let you know if it gives the results I am looking for.

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

Anonymous — Mon, 07 Sep 2020 22:26:00 GMT

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

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

Crystal_YW — Tue, 08 Sep 2020 01:00:20 GMT

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

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

Greg_Deckler — Tue, 08 Sep 2020 01:05:27 GMT

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

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

Crystal_YW — Tue, 08 Sep 2020 01:17:23 GMT

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

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

Crystal_YW — Tue, 08 Sep 2020 01:21:18 GMT

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

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

Anonymous — Tue, 08 Sep 2020 05:11:43 GMT

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

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

Crystal_YW — Tue, 08 Sep 2020 05:25:08 GMT

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

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

Anonymous — Tue, 08 Sep 2020 05:35:05 GMT

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?

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

Anonymous — Tue, 08 Sep 2020 21:05:45 GMT

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?

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

Anonymous — Tue, 08 Sep 2020 22:13:18 GMT

Thank you for the reference amitchandak however, I do not see a code for Sat-Fri.