Handling null data points
This artcile demonstrates how to handle null data points in the data visualizations. If there are rows that has no data, you could make a rule to those values in the table or chart.
Using "Missing data rule" option in the "Configure metric set element" panel window would allow you to display the data points that were hidden. By default, it is set to "None", thus, the null data points are not displayed.
2. Getting started
In this example, we will use AdventureWorks2012, with Sales.SalesPerson table. First, we will drop the SalesPerson.BusinessEntityID in the dashboard to show the rows:
Then, we drop SalesQuota in the dashboard:
Notice that BusinessEntityID 274 is missing in the list after the SalesQuota was dropped in the table.
3. Using a missing data rule
To view the missing BusinessEntityID, open the Data Analysis Panel, and edit the measure (in this case, SalesQuota).
On the "Configure Metric Set Element" window, under Metric Set Default Values, go to "Missing Data Rule". In this area, you will find options for the missing data in the data points.
Here are the options:
- None - do not display the data point
- Average - displays the average between the previous and next data points
- Average Non-Missing Data - displays the average between the previous and next non-empty data points
- Null(Empty) - displays nothing on the data point
- Null(Pass Through) - brings in only the nulls present in the database, and does not generate new hierarchy member combinations
- Rolling Average - displays the average of the previous data points
- Zero - displays zero on the missing data point
Select the Null(Empty) option. BusinessEntityID 274 shows an empty cell for SalesQuota.
Edit the measure again and indicate N/A in the Empty Value Text field. BusinessEntityID 274 shows N/A for SalesQuota.
4. Styling missing data cells
You can style cells with missing data to look different from actual data cells. Open the Properties tab for the table.
In the Look section, select Missing Data Style.
Set the Cell Color and Font Color field to distinguish cells showing data calculated by the missing data rule.
5. See also
- Video: Building a Basic Chart
- Using chart properties
- View data with a chart and drill down
- Handling null value in a state indicator column