Forum Discussion

mahendi's avatar
mahendi
Icon for Helper I rankHelper I
6 years ago
Solved

matrix total for measure

Acc_NoAmountDateName

11Saturday, June 1, 2019a
11Monday, July 1, 2019a
11Wednesday, May 1, 2019a
21Monday, July 1, 2019b
31Saturday, June 1, 2019c
31Monday, July 1, 2019c
41Wednesday, May 1, 2019d
41Saturday, June 1, 2019d
41Monday, July 1, 2019d
51Wednesday, May 1, 2019e
51Saturday, June 1, 2019e
51Monday, July 1, 2019e
61Wednesday, May 1, 2019f
61Saturday, June 1, 2019f
71Wednesday, May 1, 2019g
71Saturday, June 1, 2019g
71Monday, July 1, 2019g
81Monday, July 1, 2019h
91Wednesday, May 1, 2019i
91Saturday, June 1, 2019i
101Wednesday, May 1, 2019j

 

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.
Need Help
  • nandic's avatar
    nandic
    6 years ago

    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.

2 Replies

  • nandic's avatar
    nandic
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    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's avatar
      mahendi
      Icon for Helper I rankHelper I
      6 years ago

      nandic 

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

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