Missbehaving Matrix Drill down

StuartJohnson

New Member
Joined
Sep 13, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm looking for some help. I have a matrix table that i'm using to count the number of complaints for a given week versus last year. I can get this to work perfectly fine but because i have a completed dataset for last year and not this (i only want to show both years data to date i.e on week 12 i only show last years data up to week 12 even though i have 52 weeks i could show). I dont have to update filters every week so i've been writing measures (as i can't use a measure in a filter) to only show data up to this.
1631527838227.png

CompTWLY =
VAR MaxW = calculate(max(TotalComplaints[M&S Week]),Calendar[M&S Year] = "2021/22")
return calculate(sum(TotalComplaints[Complaints]), filter('Calendar',Calendar[M&S Year]="2020/21"), filter('Calendar',[Week]=MaxW))

For last year this works fine; However running the same measure but with the year changed does something weird, Initially the data fot This year (CompTWTY) looks correct, although i have 2 extra complaints compared to the total. However if you drill down the totals change and the information becomes more incorrect for this year but is still correct for last year?
1631527997793.png

Why would the information be incorrect but fine using the similar measure for last year.

If i change MaxW = to be a defined value such as 22 this all works correctly and if i place a card with the formula i'm currently using to define MaxW it shows the week as 22.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Couldn't you use:

calculate(sum(TotalComplaints[Complaints]), Calendar[M&S Year]="2020/21",Calendar[Week]=MaxW)

instead?
 
Upvote 0
Couldn't you use:

calculate(sum(TotalComplaints[Complaints]), Calendar[M&S Year]="2020/21",Calendar[Week]=MaxW)

instead?
Thank you but either way produces the same result. It appears that the part of the measure calculating the MaxW variable isn't working correctly on a row by row basis.
 
Upvote 0
What's the exact measure for CompTWTY? Do you have a sample file you can link to?
 
Upvote 0
THe measure for CompTWTY is almost identical to the working CompTWLY:

CompTWTY =
VAR MaxW = calculate(max(TotalComplaints[M&S Week]),Calendar[M&S Year] = "2021/22")
return calculate(sum(TotalComplaints[Complaints]), filter('Calendar',Calendar[M&S Year]="2021/21"), filter('Calendar',[Week]=MaxW))

I've bolded in Red the only difference between the working measure and the non working measure.
 
Upvote 0
Shouldn't that be 2021/22?
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top