Running Total by Group - Matrix Total

[Anonymous]

I am having issues with calculating a running total by group (sex) in a matrix. My table is an attendance sheet so I just need to calculate the running total of the number of rows (attendants) for each year. I made a calculated measure and it worked fine for the column chart. Here is the code and the chart:

Running Total by Sex = 
CALCULATE(
	COUNTROWS('tbl1'),
	FILTER(
		ALL('tbl1'),
		'tbl1'[Date] <= MAX('tbl1'[Date]) &&
		'tbl1'[Sex] = MAX('tbl1'[Sex])
	)
)

But when I converted it to a matrix, the total doesn't seem to add up properly. I put [Sex] as rows, [Date] (hierarchy) as columns and the Running Total by Sex measure as the Values. What am I doing wrong?

Thanks in advance...

[Mariusz]

Hi @Anonymous 

Try the below.

Running Total by Sex = 
CALCULATE(
	COUNTROWS('tbl1'),
	FILTER(
		ALLEXCEPT('tbl1', 'tbl1'[Sex]),
		'tbl1'[Date] <= MAX('tbl1'[Date]) &&
	)
)

Regards,
Mariusz

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

[Anonymous]

Hi @Mariusz ,

That worked!  Thanks!  

Running Total by Sex = 
CALCULATE(
	COUNTROWS('tbl1'),
	FILTER(
		ALLEXCEPT('tbl1', 'tbl1'[Sex]),
		'tbl1'[Date] <= MAX('tbl1'[Date])
	)
)

@CheenuSing : Thanks for your help.  Problem solved...

[Mariusz · Accepted solution]

@Anonymous ,

Sorry, try this

Running Total by Sex = 
CALCULATE(
	COUNTROWS('tbl1'),
	FILTER(
		ALLEXCEPT('tbl1', 'tbl1'[Sex]),
		'tbl1'[Date] <= MAX('tbl1'[Date]) 
	)
)

Regards,
Mariusz

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

[CheenuSing]

Hi @Anonymous 

Just create the measure

RunningTotal = COUNTROWS('tbl1')

Try it out and let me know.

Cheers

CheenuSing

[Anonymous]

Hi @CheenuSing ,

Thanks but it didn't work .  I get just a regular total instead of a running total now...

[CheenuSing]

Hi @Anonymous ,

Can you share the pbix in Google Drive or OneDrive and paste the link here to check and suggest.

Cheers

CheenuSing

[Anonymous]

Hi @CheenuSing ,

Sure.  I can't seem to share outside of my organization so give me a moment to figure out how to share this externally...