That makes sense. In order to get the total you want you need to change the filter context. So for each row (month and year in this case) the filter context is just that month and year and then you get a distinct count of patients in that specific month and year. When you look at the grand total it is taking all of the month and years and giving you the distinct count of patients using that context. Which is why you see 1355 since a patient can only be counted once in that context.
So what we do is change that (well every ) fitler context to take the Values of the Month year as the table for SUMX to iterate and using the [patient count] measure previously created. In the first row, May 2018 is the only value that will be iterated, so SUMX will iterate that table and return the sum of patient count. Since it’s just the one value we only get the patient count in that month year.
Now looking at the grand total, the values of year & month is all the year months. SUMX will iterate that table. It will start at May 2018, iterate over that and calculate the patient count for that month. Then it will continue on for each month and year and at the end it will take all the individual year month totals and sum them up. So what looks like the grand total is just the sum of above, is really something a little different. Everything happens in context ( filter and row ) . Also in the measure below you have either use a measure already created, or wrap it in CALCULATE as you need to invoke context transition.
Patient Count, Correct Grand Total =
SUMX(
VALUES( Source[Month & Year] ), [patient count] )
If you put Location and Year Month on Rows (with the same measure from above) you get this:
Not sure if that is what you had in mind…
I do not follow
distribution of patients by other categories not found in the sample data.
Can you post a mock-up of what you would like?
