Sum only the positive values in the column on matrix visual

[jwin2424]

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

Actual =

CALCULATE(

    SUM('Append'[Amount]),

    'Append'[Source] = "Actual"

)

Budget =

CALCULATE(

    SUM('Append'[Amount]),

    'Append'[Source] = "Budget"

)

Remaining Budget =

[Budget] - [Actual]

Budgeted Bills =

CALCULATE(

    [Budget],

    'Append'[Type] = "Bill"

)

Bills Paid =

CALCULATE(

    [Actual],

    'Append'[Type] = "Bill"

)

Remaining =

VAR _Remaining = [Budgeted Bills] - [Bills Paid]

RETURN

SUMX(

    FILTER(

        SUMMARIZE(

            'Append',

            'Append'[Sub Category],

            "Remaining2", _Remaining

        ), [Remaining2] > 0

    ), [Remaining2]

)

Then I get this: 

I would love this result:

I want to see ALL variances, but only the sum of the positives. 

 Thanks!

[PawelWrona · Accepted 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:

[v-jialongy-msft]

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.

[jwin2424]

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!

[Wilson_]

jwin2424,

Glad you got a solution to your issue. Out of curiosity, did my solution not return the correct result?

[jwin2424]

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. 

[Wilson_]

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.