Forum Discussion

SurfingData's avatar
SurfingData
Frequent Visitor
3 years ago

Measure to Total Final Amounts

Level 2Level 1 Final Amount
7353100208 38.58
7352100289 128.2
7352100293 61.08
7353100293 70.31
008358-MARS100293 83.59
7352100331 9.05
7730100331 10.6
7355100331 2.93
7353100331 10.02
7356100331 2.92

 

I am trying to sum the final amount based on the level 1 column and the current way is only summing based on level 2.

So when I put it in my report the Final Amount Tool Tip and the # Total Final Amount show the same number instead of the latter showing the aggregate. What am I missing? I have also tried Sumx and it does the same thing.

Need Help

2 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi SurfingData ，

    You could use the below to create a new column :

     

     

    sum = 
CALCULATE(SUM('Table'[Final Amount]),
FILTER(ALL('Table'),'Table'[Level 1]=EARLIER('Table'[Level 1])))

     

     

    And if you want to create a measure to get the sum,use the below:

    sum2 = CALCULATE(SUM('Table'[Final Amount]),FILTER(ALL('Table'),'Table'[Level 1]=MAX('Table'[Level 1])))

     

     

    Best Regards

    Lucien

     

    • SurfingData's avatar
      SurfingData
      Frequent Visitor
      3 years ago
      Level 3Level 2Level 1 Final Amount
      1237353100208 38.58
      1237352100289 128.2
      1237352100293 61.08
      1237353100293 70.31
      123008358-MARS100293 83.59
      1237352100331 9.05
      1237730100331 10.6
      1237355100331 2.93
      1237353100331 10.02
      1237356100331 2.92

       

      v-luwang-msftLucien, Thank you so much for taking the time to help me.

      I mis stated my problem statement. Your solution is great but aggregating the entire table( I gave only a section of my data to illustrate the problem). So all the isntances of 100331 are totalling, which is what I originally stated I needed.

      After using your solution I realized it was not actually what I needed to total by:
      I need to aggregate all the level 1 numbers by a level 3 column. I tried to modify your solution to :

       

      + Final amount sum =
      CALCULATE(SUM('Table'[Final Amount]),
      FILTER(('Table'),'Table'[Level 3]=EARLIER('Table'[Level 3])))
       
      But it is just showing blanks.
      Then I tried adding a second filter but it also did not work.
      + Final amount sum =
      CALCULATE(SUMx('Table',[Final Amount]),
      FILTER(('Table'),'Table'[Level 1]=EARLIER('Table'[Level 1])), Filter(Table,[LEvel 3]))