Use the Column Subtotals in Matrix as YTD column

[Renna]

Hi, 

I'm aiming to maintain the Column Subtotal in a Matrix as YTD, while other columns represent individual months. It works perfectly without any month filters applied (the total is sum of all months, which is coincidently YTD values):

But applying a month filter alters the Total column to reflect only the selected month(s).

Can the Total column always reflect the sum of all months, unaffected by month filters?

[Renna]

Hi @AUDISU ,
Thanks very much for your response. However it's not working 😔

[AUDISU]

Hi @Renna ,
Can you share your sample pbix file.

As I think you have added wrong field to ALL function.
Thanks

[Renna]

Hi @AUDISU ,

This is the LINK to pbix file. Thank you very much!

[AUDISU · Accepted solution]

Hi @Renna ,
Change your YTD Revenue dax as follow.

YTD Revenue = IF(ISINSCOPE('Sample Table'[Period]),

CALCULATE(SUM('Sample Table'[Revenue])),

CALCULATE(SUM('Sample Table'[Revenue]), ALL('DimDates'[Date].[Month]))

)

Link 

I only changed here.
ALL('DimDates'[Date].[Month])

Thanks

[Renna]

It works! Thank you so much!

[AUDISU]

Hi @Renna ,

use INSCOPE function.
Dax query should be like follow.

Change the table and column names as your data model.
Amount = IF(INSCOPE(Calender[Month]) , CALCULATE(SUM('Sales'[SalesAmount])),CALCULATE(SUM('Sales'[SalesAmount]) , ALL(Calender[Month]))

Thanks
DISU.