How to Make Measures Total Correctly in Power BI Tables
Microsoft Power BI is a great tool for creating and publishing dynamic and interactive reports, but it has a few quirks that can be tricky to navigate. When creating a Power BI report with a table that contains Measures, sometimes the columns in the table don’t total correctly. When a Measure is used on rows in a table, the column total for those rows is not calculated based on a sum of the results in the rows, but instead it calculates using the same Measure formula and applies it to the total of the data selected.
To correct the totals not calculating correctly in Power BI, you will need to create a secondary Measure that works around the default totaling calculation.
In this example, the table shows a list of projects, the budgets for the projects, the expenses for the projects, and the amount of budget each project has remaining. The Budget Remaining column is calculated using a Measure that takes the Budget Amount and subtracts out the Posted Expenses. Since the Measure is calculated on each row, the table shows an incorrect total for the Budget Remaining column.
Budget Remaining Measure formula:
Budget Remaining = IF(SUM([Budget Amount])=0,BLANK(),(SUM([Budget Amount]))-SUM([Posted Expenses]))
In this table, the Total displayed for the Measure for Budget Remaining is not summing the column; instead, it is taking the total Budget Amount sum for all rows and subtracting the total Posted Expenses sum for all rows. Since two of the projects in the table don’t have a budget, the Total displayed of $1,605 is not the true total of the Budget Remaining column.
To correct this, you need to create a second Measure as a workaround since Measures calculate by line on the table. You can use a Measure that uses an IF statement and counts the rows to find whether it is a totaling row (meaning it won’t be counted) or not. Alternatively, you can use the HASONEFILTER formula to do the same thing. You will keep the original Measure formula the same, and this second one will build off the original.
For the workaround Measure, use an IF statement to state if the row can be counted because it has values then provide the original Measure of Budget Remaining. If the row in the table can’t be counted (because it is a totaling row), then use a SumX formula:
Budget Remaining Correct = IF(COUNTROWS(VALUES([Project No]))=1,[Budget Remaining],SUMX(VALUES([Project No]),[Budget Remaining])
You can also use the HASONEFILTER version of this solution to get the same result. This formula looks for rows with one filter rather than counting the rows with values:
Budget Remaining Correct = IF(HASONEFILTER([Project No]),[Budget Remaining],SUMX(VALUES([Project No]),[Budget Remaining]))
Either formula will usually get you the correct totals. You might find that one works and one doesn’t, depending on the complexity of your data.
You will now see in the table the original Budget Remaining Measure, which shows an incorrect total, and the secondary workaround Measure Budget Remaining Correct, which shows the correct total.
To clean up the report, rename your two Measures and remove the original Measure from the table so that only the workaround correct Measure shows on the table.
Now you have a table in your Power BI report that displays a correct total for rows that use a calculated Measure.
You can find more information from Microsoft about the DAX formulas used in this blog here:
IF https://docs.microsoft.com/en-us/dax/if-function-dax
SUM https://docs.microsoft.com/en-us/dax/sum-function-dax
COUNTROWS https://docs.microsoft.com/en-us/dax/countrows-function-dax
SUMX https://docs.microsoft.com/en-us/dax/sumx-function-dax
VALUES https://docs.microsoft.com/en-us/dax/values-function-dax
HASONEFILTER https://docs.microsoft.com/en-us/dax/hasonefilter-function-dax
BLANK https://docs.microsoft.com/en-us/dax/blank-function-dax
To find out more about utilizing Power BI in your business, how Power BI integrates with Dynamics NAV and Dynamics 365 to create dynamic real-time reporting options, or to get help with building your next Power BI report, contact our team today at contact ArcherPoint.
Read more “How To” blogs from ArcherPoint for practical advice on using Power BI, Microsoft Dynamics 365 Business Central, or NAV.