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
jwin2424
Resolver I
Resolver I

Sum only the positive values in the column on matrix visual

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, 2024AutoGasBill100Budget
Monday, January 1, 2024AutoInsuranceBill180Budget
Monday, January 1, 2024AutoLoanBill400Budget
Monday, January 1, 2024RentMortgageBill2000Budget
Monday, January 1, 2024UtilitiesComcastBill80Budget
Monday, January 1, 2024UtilitiesElectricityBill95Budget
Monday, January 1, 2024UtilitiesWaterBill125Budget
Monday, January 1, 2024UtilitiesGasBill45Budget
Monday, January 1, 2024GroceriesFred MeyerExpense800Budget
Monday, January 1, 2024GroceriesCostcoExpense400Budget
Monday, January 1, 2024HouseholdAmazonExpense300Budget
Monday, January 1, 2024HouseholdTargetExpense100Budget
Monday, January 1, 2024Entertainment Expense100Budget
Monday, January 1, 2024Games Expense50Budget
Monday, January 1, 2024Gift Expense300Budget
Monday, January 1, 2024AutoGasBill25Actual
Friday, January 5, 2024AutoInsuranceBill85Actual
Monday, January 1, 2024AutoLoanBill700Actual
Wednesday, January 3, 2024RentMortgageBill2001Actual
Monday, January 1, 2024UtilitiesComcastBill90Actual
Monday, January 15, 2024UtilitiesElectricityBill44Actual
Thursday, January 18, 2024UtilitiesWaterBill78Actual
Monday, January 1, 2024UtilitiesGasBill43Actual
Friday, January 12, 2024GroceriesFred MeyerExpense434Actual
Monday, January 1, 2024GroceriesCostcoExpense121Actual
Thursday, January 11, 2024HouseholdAmazonExpense500Actual
Monday, January 1, 2024HouseholdTargetExpense34Actual
Monday, January 1, 2024Entertainment Expense5Actual
Monday, January 1, 2024Games Expense5Actual
Monday, January 1, 2024Gift Expense5Actual

 

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: 
 
jwin2424_3-1716242030307.png

I would love this result:

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

jwin2424_4-1716242119968.png

 

 Thanks!
1 ACCEPTED SOLUTION
PawelWrona
Resolver I
Resolver I

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:

PawelWrona_0-1716268730251.png

 

View solution in original post

6 REPLIES 6
PawelWrona
Resolver I
Resolver I

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:

PawelWrona_0-1716268730251.png

 

v-jialongy-msft
Community Support
Community Support

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:

vjialongymsft_0-1716255795154.png

 

 

 

 

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?

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_
Memorable Member
Memorable Member

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.