matrix total for measure

[mahendi]

Acc_NoAmountDateName

1	1	Saturday, June 1, 2019	a
1	1	Monday, July 1, 2019	a
1	1	Wednesday, May 1, 2019	a
2	1	Monday, July 1, 2019	b
3	1	Saturday, June 1, 2019	c
3	1	Monday, July 1, 2019	c
4	1	Wednesday, May 1, 2019	d
4	1	Saturday, June 1, 2019	d
4	1	Monday, July 1, 2019	d
5	1	Wednesday, May 1, 2019	e
5	1	Saturday, June 1, 2019	e
5	1	Monday, July 1, 2019	e
6	1	Wednesday, May 1, 2019	f
6	1	Saturday, June 1, 2019	f
7	1	Wednesday, May 1, 2019	g
7	1	Saturday, June 1, 2019	g
7	1	Monday, July 1, 2019	g
8	1	Monday, July 1, 2019	h
9	1	Wednesday, May 1, 2019	i
9	1	Saturday, June 1, 2019	i
10	1	Wednesday, May 1, 2019	j

Measure = VAR maxDate=MAX(Sheet1[Date]) RETURN CALCULATE(SUM(Sheet1[Amount]),ALL(Sheet1[Date].[Date]),Sheet1[Date].[Date]<=maxDate,Sheet1[Acc_No]<=7)

Measure 2 = CALCULATE(SUM(Sheet1[Amount]),Sheet1[Acc_No]>7)

Measure 3 = IF(ISBLANK([Measure]),[Measure 2],[Measure])

The third measure is a combination of other two measures but its total only shows measure 1 total.

I need cumulative sum of some accounts and month wise sum of other accounts.

Please help suggest some solution for this.

[nandic · Accepted solution]

@mahendi ,

Here is the formula:

Measure 4 = IF(HASONEVALUE(Sheet1[Acc_No]),IF(ISBLANK([Measure]),[Measure 2],[Measure]),[Measure]+[Measure 2])

It checks if row is showing total or not. If it is total then sum these measures.

[mahendi]

@nandic 

Thank you! I spent 4 days to try and solve this issue.

This formula solved it for me, was a huge help. 👍