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

## Sum of column with repeating values

Hello all and thank you in advance for any help,

I have a financial spreadsheet which has expenditures being charged to an assortment of task codes. This spreadsheet has a budget collumn. Each time a task code is charged the budgeted amound populates. So if I am looking to get the total amount budgeted with:

`TotalBudget = Sum(Detail[Budget])`

I get an astrinomical amount.

How do I code it to sum the budget for each task code just once?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

It sounds like your total Budget for each Task Code is being duplicated on each expenditure Detail record?

Can you move your Budget data out to a separate table and remove duplicates?

Alternately, you'll need a (potentially slow) measure that does that for you, something like:

```Total Budget =
IF (
HASONEVALUE ( Detail[Task Code] ),  // On a row for a Task Code
MAX ( Detail[Budget] ),
SUMX (                              // On a total row, so need to filter the rows then add
SUMMARIZE ( Detail, Detail[Task Code], Detail[Budget] ),
Detail[Budget]
)
)```

4 REPLIES 4
Microsoft Employee

Hi @nlenzi,

I am not able to reproduce your scenario based on the description. Could you please share some sample data or screenshot for further analysis?

You'd better create add a filter in the measure if you want to calculate the sum of each code. You can use the following formula and check if it works.

`TotalBudget1 = CALCULATE(Sum(Detail[Budget]),ALLEXCEPT(Detail,Detail[Code column hearder]))`

Best Regards,
Angelia

Frequent Visitor

Here is a sample of the data. As you can see it lists the total budget for task 09..ww.RB.00 on every line item, and the same for 09.PW.MC.00. I want the total budget which I could achieve by adding each value once, not every line.

Anonymous
Not applicable

It sounds like your total Budget for each Task Code is being duplicated on each expenditure Detail record?

Can you move your Budget data out to a separate table and remove duplicates?

Alternately, you'll need a (potentially slow) measure that does that for you, something like:

```Total Budget =
IF (
HASONEVALUE ( Detail[Task Code] ),  // On a row for a Task Code
MAX ( Detail[Budget] ),
SUMX (                              // On a total row, so need to filter the rows then add
SUMMARIZE ( Detail, Detail[Task Code], Detail[Budget] ),
Detail[Budget]
)
)```

Frequent Visitor

I believe this worked! Thank you Steve!

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.