{"id":12359,"date":"2023-06-28T12:08:10","date_gmt":"2023-06-28T12:08:10","guid":{"rendered":"https:\/\/monocroft.com\/?p=12359"},"modified":"2023-06-28T12:08:10","modified_gmt":"2023-06-28T12:08:10","slug":"replacing-blank-calculated-row-values-with-a-sum-in-power-bi","status":"publish","type":"post","link":"https:\/\/monocroft.com\/replacing-blank-calculated-row-values-with-a-sum-in-power-bi\/","title":{"rendered":"Replacing Blank Calculated Row Values with a Sum in Power BI"},"content":{"rendered":"\n
During analysis, dealing with blank or null values in calculated rows can be a big problem. However, Power BI provides several techniques to address this, and one of such is to replace the blank calculated row values with a sum.<\/p>\n\n\n\n
In this guide, I will walk you through the process of replacing blank calculated row values with sums in Power BI, to ensure consistent and reliable data analysis.<\/p>\n\n\n\n
Let’s get started!<\/p>\n\n\n\n
When working with datasets in Power BI, it is normal to come across blank or null calculated row values, which is often due to missing or incomplete data \u2014 and ignoring them can lead to incorrect analysis and visualizations.<\/p>\n\n\n\n
Thus, for a more accurate analysis, it is important to replace these blank or null values with the appropriate sum, especially when you are working with large datasets. <\/p>\n\n\n\n
However, to avoid further errors, automating the process saves some time and leads to a more accurate analysis.<\/p>\n\n\n\n
To replace a blank calculated row value, you will use the DAX (Data Analysis Expressions) \u2014 a formula language used for creating custom calculations.<\/p>\n\n\n\n
Now, let’s go through the steps, using DAX formulas to replace blank calculated row values with a sum in Power BI.<\/p>\n\n\n\n
The first step is to identify blank or null calculated row values in your dataset. You can use the below formula:<\/p>\n\n\n\n
Blanks = IF(ISBLANK([MyColumnName]), \"Blank value found\", \"No blank value\")<\/code><\/strong><\/pre>\n\n\n\nReplace \"MyColumnName<\/strong>\"<\/code> with the name of your calculated column.<\/em><\/p>\n\n\n\nThis formula will indicate if there are blank values in the calculated column or not, indicating with either “Blank value found” or “No blank value”<\/strong><\/p>\n\n\n\n2. Create a calculated column to calculate the non-blank values<\/h3>\n\n\n\n
After you identify the blank calculated rows, next is to create a measure to calculate the sum of non-blank values.<\/p>\n\n\n\n
To do this, go to the “Modeling” tab and click on “New column.” You can enter a name for your measure (e.g., “SumofColumn”). Then use the formula below:<\/p>\n\n\n\n
SumofColumn = SUMX(FILTER('MyTable', NOT(ISBLANK([MyColumn]))), [MyColumn])<\/code><\/strong><\/pre>\n\n\n\nThe FILTER <\/strong>function creates a table that removes blank values from the calculated column. The NOT(ISBLANK())<\/strong><\/code> function checks whether [MyColumn]<\/strong><\/code> values are blank, and the SUMX <\/strong><\/code>function sums the non-blank values.<\/p>\n\n\n\n3. Replace blank calculated row values with the sum using a calculated column<\/h3>\n\n\n\n
Now, you need to create another calculated column to replace the blank values with the sum. To achieve this, go to the “Modeling” tab and click on “New column” and give it a special name (e.g., “ModifiedColumn”). <\/p>\n\n\n\n
Then enter the below formula in the formula bar:<\/p>\n\n\n\n
ModifiedColumn = IF(ISBLANK([MyColumn]), [SumofColumn], [MyColumn])<\/code><\/strong><\/pre>\n\n\n\nThe formula uses the IF <\/strong>function to check if the original column is blank. It then replaces the blank value with the sum of non-blank values calculated in the previous step if it is. Else, it retains the original value.<\/em><\/p>\n\n\n\nNow that you have modified the formula, you can click on the “Check Mark” icon or press “Enter” on your computer to apply the changes.<\/p>\n\n\n\n
Power BI automatically recalculates the values based on the modified formula, and will replace the blank calculated row values with the sum of non-blank values.<\/p>\n\n\n\n
4. Test and validate the modified DAX formula<\/h3>\n\n\n\n
Once you create the new calculated column, you must test and validate its accuracy. Testing involves applying the formula to subsets of your data while comparing the results with the expected outcome. And to validate it, you must ensure it works perfectly.<\/p>\n\n\n\n
Power BI offers different features like “Data Analysis” and “Power Query Editor” functions that can help you test and validate your DAX formula.<\/p>\n\n\n\n
5. Apply the formula relevant visuals in Power BI<\/h3>\n\n\n\n
After you test and validate your formulas and confirm they are working perfectly, you can then apply them to all relevant visuals in your Power BI reports. <\/p>\n\n\n\n
This ensures that the blank calculated row values are replaced with the calculated sum across all visuals, such as charts and tables.<\/p>\n\n\n\n
6. Refresh and update the Power BI report<\/h3>\n\n\n\n
Finally, after applying the formula to all relevant visuals, the last step is to refresh your Power BI report to reflect new calculations to ensure the replaced values are consistent and up-to-date.<\/p>\n\n\n\n
With Power BI “Refresh visuals” button under the “Optimize” tab, you can easily refresh your report!<\/p>\n\n\n
\n