Upcoming webinar on 'Inforiver Charts : The fastest way to deliver stories in Power BI', Aug 29th , Monday, 10.30 AM CST.    Register Now

Calculate remaining budget in Power BI (without using DAX)

by Inforiver | Nov 15, 2021 | ,

In Microsoft Power BI, you may often encounter incorrect total values while using calculated measures. Here is one such example. We have budget amounts for a few products (Budget Amount) and have incurred expenses against each (Posted Expenses). Our objective is to calculate remaining budget in a new column, and add them up.

Budget and Posted expenses

Let us calculate Remaining Budget using the formula = Posted Expenses – Budget Amount. We need to do this only for rows where expenses don’t exceed the budget (else we will have negative remaining budgets). The correct logical expression is IF (Budget < Expenses, “”, Budget-Expenses)

The DAX equivalent is

Budget Remaining = IF (SUM ([Budget Amount])

Calculating remaining budget with DAX

However, notice that the total budget is incorrect. Instead of showing 4000 (sum of individual records), it shows 2500 (the difference between Budget Amount Totals and Posted Expenses Totals).

The traditional fix for this using DAX has been as follows.

Calculate remaining budget using DAX

There are several ways to do this – using COUNTROWS or HASONEFILTER:

Budget Remaining Correct = IF (COUNTROWS (VALUES ([Product ID])) =1, [Budget Remaining], SUMX (VALUES ([Product ID]), [Budget Remaining])
Budget Remaining Correct = IF (HASONEFILTER ([Product ID]), [Budget Remaining], SUMX (VALUES ([Product ID]), [Budget Remaining]))
calculate with DAX

There are other solutions such as using virtual tables based on the complexity of your requirements. But there is one other solution – which is most straightforward – using Inforiver.

Calculate remaining budget without using DAX – The Inforiver Way

In Inforiver, we create a formula measure using a simple IF statement.

Formula with IF statement

There is just one more setting that you need to turn on – the Row Aggregation Type. By default, it is set to Formula – meaning, it applies the same formula on totals & subtotals. But we want to avoid just that.

Now change the Row Aggregation Type to type Sum – This ensures that the individual rows are simply added up to calculate totals. There are few other row aggregation types as well that you can try out, depending on your need.

Row aggregation type

And we get the values right, and this method is a lot faster than using DAX.

Calculate remaining budget in Power BI using Inforiver

Watch this video how to efficiently allocate budgets without using DAX

Start a free trial of Inforiver today!

Learn more about visual calculations in Inforiver here. Interested to know more about Budgeting in Power BI click here.

Share this on:

Get Inforiver brochure

Maximize your business potential with Inforiver's paginated reporting, data entry, planning & budgeting capabilities
Download now
About Inforiver!
Inforiver is the fastest way to do everything in Power BI. It enables citizen developer productivity and unleashes true self-service with our intuitive and interactive no-code data app suite for Microsoft Power BI. The product is developed by Lumel Technologies Inc, who are #1 Power BI Visuals AppSource Partner serving over 3,000+ customers worldwide with their xViz, Inforiver, and ValQ offerings.
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram