Measure to Total Final Amounts

[SurfingData]

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
7730	100331		10.6
7355	100331		2.93
7353	100331		10.02
7356	100331		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.

[v-luwang-msft]

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]

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]))