Necessary cookies are absolutely essential for the website to function properly.
Read more “How To” blogs from ArcherPoint for practical advice on using Power BI, Microsoft Dynamics 365 Business Central, or NAV. 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. You can find more information from Microsoft about the DAX formulas used in this blog here: Now you have a table in your Power BI report that displays a correct total for rows that use a calculated Measure. Figure 3 – Power BI Table with Measure totals 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. 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. Figure 2 – Power BI table with corrected Measure totals You might find that one works and one doesn’t, depending on the complexity of your data. This formula looks for rows with one filter rather than counting the rows with values:īudget Remaining Correct = IF(HASONEFILTER(),SUMX(VALUES(),))Įither formula will usually get you the correct totals. You can also use the HASONEFILTER version of this solution to get the same result. If the row in the table can’t be counted (because it is a totaling row), then use a SumX formula:īudget Remaining Correct = IF(COUNTROWS(VALUES())=1,SUMX(VALUES(),) You will keep the original Measure formula the same, and this second one will build off the original.įor 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. Alternatively, you can use the HASONEFILTER formula to do the same thing. 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. To correct this, you need to create a second Measure as a workaround since Measures calculate by line on the table. 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. 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 the Measure is calculated on each row, the table shows an incorrect total for the Budget Remaining column.īudget Remaining = IF(SUM()=0,BLANK(),(SUM())-SUM()) Figure 1 – Power BI table where the Measure total is incorrect The Budget Remaining column is calculated using a Measure that takes the Budget Amount and subtracts out the Posted Expenses. 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. 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. 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. When creating a Power BI report with a table that contains Measures, sometimes the columns in the table don’t total correctly.
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.