Matrix with different values in the total row (i.e. mix total with average and sum changes)

[bawpie]

Hello,

I have created a matrix table in Power BI desktop that has the month name in the rows, and the financial year in the columns based on a calendar table that has both of these set in a heirachy. 

There are 4 measures placed in the values section- cases starting, cases ending, cases as at, and case change.  I'd like to include some totals in my matrix - this isn't an issue for cases starting and cases ending, but the cases as at figures is a total of the as at figures for each month (I'd prefer to show the March as at figure for each year here) and the change figure isn't a figure I recognise at all though it may be based on how the measure is written (a subtraction of the as at count - the previous month as at.)  

The table on the left is how it looks in power BI desktop currently (note the issues highlighted in red) and the table on the right is how I would like it to look (suppress the change figure for April as there is no prior data to compare) and change the as at and change totals.  

Measures are written as follows:

Starting = count('Case start'[ID])

Ending = count('Case end'[ID])

As_at =  count('Case as at'[ID])

Change = 'Case as at'[As_at] - 'Case as at'[Previous_Month_As_At]

Below is a helper measure to calculate the change measure.

Previous_Month_As_At = CALCULATE([As_at],PREVIOUSMONTH('Calendar'[MONTH_END]))

Any advice on how to achieve this would be appreciated!

Thanks

[bawpie]

I was able to resolve this by bringing the prior month's data into the query, then just filtering the visual to only return the month's I was interested in.  The prior month was taken into account in the dataset, so the red 344 was no longer returned.  

[VijayP · Accepted solution]

@bawpie On top of the regular formula what i gave you can use another if logic based on the requirement and convert into zero using *0 . that should work

[VijayP]

@bawpie 
try using this formula 

As At Count = IF(ISFILTERED(Dates[Month]), COUNT('Data Table'[as at]),CALCULATE(COUNT('Data Table'[as at]),FILTER(Dates,Dates[MonthNo.]=MAX(Dates[MonthNo.]))))

This file may help you! https://drive.google.com/file/d/1K_l58hIpNONuNU06X4_b2BUeLvN_w4Xv/view?usp=sharing

[bawpie]

Thanks, the file was really useful.  I changed my as at count to the following:

As At Count = IF(ISFILTERED('Calendar'[MONTH_NAME_SHORT]), COUNT('CP as at'[ARR_ID]),CALCULATE(COUNT('CP as at'[ARR_ID]),FILTER('Calendar','Calendar'[FY_MONTH_NUMBER]=MAX('Calendar'[FY_MONTH_NUMBER]))))

and my change measure to : 

Change = [As At Count] - CALCULATE([As At Count], PREVIOUSMONTH('Calendar'[MONTH_END]))

This is now working almost perfectly, the only issue being the initial change value which shows as 344 in red in the initial example.  I think it could possible be achieved by updating the change measure to return 0 if the previous month is the min previous month, which the following achieves:

Change =
IF(
ISBLANK(PREVIOUSMONTH('Calendar'[MONTH_END])) && ISFILTERED('Calendar'[MONTH_NAME_SHORT]),
0,
[As At Count] - CALCULATE([As At Count], PREVIOUSMONTH('Calendar'[MONTH_END]))
)

However, the total change figure is still taking into account the red 344, so it doesn't quite work.