Matrix Visual Columns Total formatting

[BasilRudham]

Hi All, I need some help on what I think is a simple solution. I have a Matrix visual with the column Total. First I would like to exclude the totals of the last row which shows %'s from the Totals column. Secondly, how do I keep the Total as YTD when I filter the columns down to the last 3 calendar months only?

This the first time I'm using this forum so please excuse any errors/ignorance of protocols, etc. Any assistance would be appreciated. 

[v-jianboli-msft]

Hi BasilRudham,

Approve with @amitchandak .

Please try:

Create a measure:

Measure =

var _a = IF(MAX('Table'[Cyclecount])<>"Percentage Difference between Months",SUMX(FILTER( ALL('Table'),[Cyclecount]=MAX('Table'[Cyclecount])),[Value]),BLANK())

return IF(ISINSCOPE('Date'[Date]),MAX('Table'[Value]),_a)

Then apply the measure to the value field:

And use a slicer instead of filter

Final output:

Besides, it seems that there are hierarchies in your Matrix, what is that?

If the result is not what you want, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data. 

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BasilRudham]

Good Day amitchandak, Thanks for the reply. I'm a relative newbie so I'm afraid I have no knowledge of how this works. The matrix table is populated with the fields 'Cyclecount' and 'Values' from the data table 'tbl_cycle_02' and the columns are populated from the 'Dates' table using the field 'MonthYear'. Is this the 'Table[Month] you are referring to? What is the [Percent Measure] you are referring to? Do i need to create a measure to somehow refer to the row containing the percentages and use this in your measure? Thank you for the assistance so far.

[amitchandak]

@BasilRudham , You need a measure like

if(isinscope(Table[Month]), [Percent Measure], blank())

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

[amitchandak]

@BasilRudham , You need a measure like

if(isinscope(Table[Month]), [Percent Measure], blank())

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/