Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I am working on a budget report, and I need to see my remaining bills. Some bills come in for more than budgeted, so when my formula [Budget] - [Actual] is calculated, the value is negative. When I try to see how much I left in expected bills, that negative value is understanding how much I have left.
Here is my sample table:
DatePrimary CategorySub CategoryTypeAmountSource
Monday, January 1, 2024 | Auto | Gas | Bill | 100 | Budget |
Monday, January 1, 2024 | Auto | Insurance | Bill | 180 | Budget |
Monday, January 1, 2024 | Auto | Loan | Bill | 400 | Budget |
Monday, January 1, 2024 | Rent | Mortgage | Bill | 2000 | Budget |
Monday, January 1, 2024 | Utilities | Comcast | Bill | 80 | Budget |
Monday, January 1, 2024 | Utilities | Electricity | Bill | 95 | Budget |
Monday, January 1, 2024 | Utilities | Water | Bill | 125 | Budget |
Monday, January 1, 2024 | Utilities | Gas | Bill | 45 | Budget |
Monday, January 1, 2024 | Groceries | Fred Meyer | Expense | 800 | Budget |
Monday, January 1, 2024 | Groceries | Costco | Expense | 400 | Budget |
Monday, January 1, 2024 | Household | Amazon | Expense | 300 | Budget |
Monday, January 1, 2024 | Household | Target | Expense | 100 | Budget |
Monday, January 1, 2024 | Entertainment | Expense | 100 | Budget | |
Monday, January 1, 2024 | Games | Expense | 50 | Budget | |
Monday, January 1, 2024 | Gift | Expense | 300 | Budget | |
Monday, January 1, 2024 | Auto | Gas | Bill | 25 | Actual |
Friday, January 5, 2024 | Auto | Insurance | Bill | 85 | Actual |
Monday, January 1, 2024 | Auto | Loan | Bill | 700 | Actual |
Wednesday, January 3, 2024 | Rent | Mortgage | Bill | 2001 | Actual |
Monday, January 1, 2024 | Utilities | Comcast | Bill | 90 | Actual |
Monday, January 15, 2024 | Utilities | Electricity | Bill | 44 | Actual |
Thursday, January 18, 2024 | Utilities | Water | Bill | 78 | Actual |
Monday, January 1, 2024 | Utilities | Gas | Bill | 43 | Actual |
Friday, January 12, 2024 | Groceries | Fred Meyer | Expense | 434 | Actual |
Monday, January 1, 2024 | Groceries | Costco | Expense | 121 | Actual |
Thursday, January 11, 2024 | Household | Amazon | Expense | 500 | Actual |
Monday, January 1, 2024 | Household | Target | Expense | 34 | Actual |
Monday, January 1, 2024 | Entertainment | Expense | 5 | Actual | |
Monday, January 1, 2024 | Games | Expense | 5 | Actual | |
Monday, January 1, 2024 | Gift | Expense | 5 | Actual |
I would love this result:
I want to see ALL variances, but only the sum of the positives.
Solved! Go to Solution.
You were very close with your code. The difference you are calculating is calculated outside of the context of summarized table, and since it is a Variable, the same value is simply copied for each row. You could try this one instead:
Remaining =
VAR summarizedTable =
ADDCOLUMNS(
DISTINCT('Append'[Subcategory]),
"@Remaining", [Budgeted Bills] - [Bills Paid]
)
VAR result =
SUMX(
FILTER(summarizedTable, [@Remaining] > 0),
[@Remaining]
)
RETURN {result}
And here is the result:
You were very close with your code. The difference you are calculating is calculated outside of the context of summarized table, and since it is a Variable, the same value is simply copied for each row. You could try this one instead:
Remaining =
VAR summarizedTable =
ADDCOLUMNS(
DISTINCT('Append'[Subcategory]),
"@Remaining", [Budgeted Bills] - [Bills Paid]
)
VAR result =
SUMX(
FILTER(summarizedTable, [@Remaining] > 0),
[@Remaining]
)
RETURN {result}
And here is the result:
Hi @jwin2424
Please try the following dax:
Remaining =
VAR _PositiveVariances =
FILTER(
SUMMARIZE(
'Append',
'Append'[Sub Category],
"RemainingPositive", [Budgeted Bills] - [Bills Paid]
),
[RemainingPositive] > 0
)
RETURN
SUMX(_PositiveVariances, [RemainingPositive])
This is the result you want:
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This worked as well. I chose the other as the solution just due to the additional explination as to why mine didnt work. Thank you for this though!
jwin2424,
Glad you got a solution to your issue. Out of curiosity, did my solution not return the correct result?
Proud to be a Super User! | |
Actually this did work as well. This one provided 0s as well in place of non-positive values. I chose the post I did for the answer due to the explination attached to it explaining why my wasn't working.
Hey jwin2424,
Try this:
Remaining =
SUMX (
VALUES ( 'Append'[Sub Category] ),
MAX ( [Budgeted Bills] - [Bills Paid], 0 )
)
If that doesn't work, can you share a screenshot of your data model?
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.
Proud to be a Super User! | |
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
60 | |
57 |