Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register 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! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
219 | |
89 | |
76 | |
67 | |
60 |