Sum of column with repeating values

[nlenzi]

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? 

[v-huizhn-msft]

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

[nlenzi]

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 · Accepted solution]

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

[nlenzi]

I believe this worked! Thank you Steve!