March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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?
Solved! Go to 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] ) )
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.
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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
124 | |
89 | |
86 | |
70 | |
51 |
User | Count |
---|---|
206 | |
150 | |
97 | |
78 | |
69 |