If row subtotal of Matrix is greater than 100 then return True

[AidanW99]

Hello I have a matrix table that resembles the following:

Film Name	Genre	Age Rating	Cinema	Jan-24	Feb-24	Mar-24	Apr-24
Barbie	Comedy	PG	London	70	50	80	10
			Glasgow	40	20	40	30
Ghostbusters	Action	PG	Manchester	30	50	20	90
			Cardiff	10	80	40	100
Batman	Superhero	12	London	40	40	50	20

What I want is to create a Measure that filters all the month columns by whether or not the row subtotal adds up to over a 100 (e.g. Barbie in Jan and Ghostbusters in April, etc, should be shown). I have tried the following but it seems to calculate the 'viewers' for ALL the month columns rather than each of the columns individually:

if(CALCULATE(
            SUM('Films'[Viewers]),
            KEEPFILTERS(Film[Month] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1)),
            ALL('Film'[Film Name])
        )>1,1,0)

   Not sure if I have explained this well, but any help will be appreciated 🙂

[Anonymous]

Hi @AidanW99 ,

Can you give me more about the Matrix's details or the .pbix file?

I currently create a table and create a measure.

Measure =
VAR _Jan =
    SUM ( 'Table'[Jan-24] )
VAR _Feb =
    SUM ( 'Table'[Feb-24] )
VAR _Mar =
    SUM ( 'Table'[Mar-24] )
VAR _Apr =
    SUM ( 'Table'[Apr-24] )
RETURN
    IF ( _Jan > 100 || _Feb > 100 || _Mar > 100 || _Apr > 100, 1, 0 )

Then I can get the result.

Best Regards

Yilong Zhou

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

[AidanW99]

There is quite a few date columns so im not sure if this would be the best method. There's also over 1000 rows.

The date months range from 2022 up to 2027. 

[Anonymous]

Hi @AidanW99 ,

I know you have many rows, so can you provide more details with your desired output and pbix file without privacy information (or some sample data)?

Best Regards

Yilong Zhou