Measure to calculate column % per row in a matrix

[JamieH]

I have a matrix that has the following attributes:

Rows - 

   BookingMonth

   AccStatus

Columns - 

   MonthsSinceBooking

Values - 

   Balance

So essentially, for each booking month, I can see the different AccStatus for each month since booking.  This all works fine when just showing the balance.  

What I would also like to see though, is the percent for each AccStatus in the BookingMonth by each MonthSinceBooking.

As an example, this is a made up view of what the matrix looks like now:

BookingMonth	1	2	3
July 2022	100	100	100
Status A	90	80	70
Status B	10	10	18
Status C	0	10	12
August 2022	250	250
Status B	150	175
Status D	100	75

This is what I would like it to show:

BookingMonth	1	2	3
July 2022	100%	100%	100%
Status A	90%	80%	70%
Status B	10%	10%	18%
Status C	0%	10%	12%
August 2022	100%	100%
Status B	60%	70%
Status D	40%	30%

Can someone please advise on how to create a measure that would show this please?

Thanks

[v-mengzhu-msft]

Hi @JamieH ,

You can directly through this option to change the data value to percentage mode.

Best regards,

Community Support Team Selina zhu

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

[JamieH]

Hi there, 

Yes, this doesnt work though.  Because I have 2 variables in my Rows element (BookingMonth and AccStatus), none of the percentage options breakdown each bookingMonth into 100% intervals.   It does it for the whole matrix 

[JamieH]

And no, BookingMonth comes on the one table with all the other data that makes up this matrix.  So no Date Table.

[JamieH]

Thanks Amit. 

No, no measure used for M1/M2 etc.  The data comes in with a row for each Month.

[amitchandak]

@JamieH , Assume you have a measure M1 used in matrix

then you can try  a measure like

divide([M1], calculate([M1], removefilters(Table[Status])) )

if you are using date table for the month year

divide([M1], calculate([M1], datemtd(endofmonth(Date[Date])))  )

Percent of Total and Percent of SubTotal https://www.youtube.com/watch?v=6jTildcV2ho