Skip to main content
cancel
Showing results for 
Search instead 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

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

 

 

View solution in original post

4 REPLIES 4
v-huizhn-msft
Microsoft Employee
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

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. 

Capture.PNG

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

 

 

I believe this worked! Thank you Steve!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors