Forum Discussion
Measure to Total Final Amounts
Level 2 Level 1 Final Amount 7353 100208 38.58 7352 100289 128.2 7352 100293 61.08 7353 100293 70.31 008358-MARS 100293 83.59 7352 100331 9.05 ...
v-luwang-msft
Community Support
Community SupportHi 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
Level 3 Level 2 Level 1 Final Amount 123 7353 100208 38.58 123 7352 100289 128.2 123 7352 100293 61.08 123 7353 100293 70.31 123 008358-MARS 100293 83.59 123 7352 100331 9.05 123 7730 100331 10.6 123 7355 100331 2.93 123 7353 100331 10.02 123 7356 100331 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]))
