Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Running Total by Group - Matrix Total

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...

 

 

 

Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    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.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous 

     

    Just create the measure

     

    RunningTotal = COUNTROWS('tbl1')

     

    Try it out and let me know.

     

    Cheers

     

    CheenuSing

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Anonymous ,

       

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

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Anonymous ,

         

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

         

         

        Cheers

         

        CheenuSing

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    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.

    • Mariusz's avatar
      Mariusz
      Community Champion
      7 years ago

      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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Mariusz ,

       

      That worked!  Thanks!  

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

      Anonymous : Thanks for your help.  Problem solved...