cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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 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.

2 REPLIES 2
Community Support

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

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.