topic Power KPI Matrix in Custom Visuals Development Discussion

Power KPI Matrix

uvga — Tue, 17 Jun 2025 10:26:46 GMT

Improve Handling of Missing Data in Sparklines – Option to Show Zeros or Use Column Chart

Description:

I'm using Power KPI Matrix to show two measures as sparklines over a date axis:

End Balance Quantity
Sales Quantity

While the visual works well for End Balance Quantity (which typically has values on most dates), I’m running into issues with Sales Quantity, where data is often missing on some dates (i.e., no sales occurred).

Currently, the visual connects the dots between existing sales data points, even across these gaps, which makes the line chart visually misleading—as if there were continuous activity.

🔧 Suggestions:

Add an option to control how missing values are treated, such as:
- Convert missing values to zero or blank
- Prevent line interpolation across missing dates (break the line)
Allow a different visual representation per measure, e.g.:
- Keep End Balance Qty as a line
- Show Sales Qty as a column/bar chart

This would effectively make it a combo chart within the matrix, which would be more appropriate for measures with very different data behaviors.

Adding these options would greatly enhance visual clarity and accuracy, especially for datasets with sparse or irregular data like sales transactions.

Thank you for your great work on this visual!

Re: Power KPI Matrix

grognard — Tue, 17 Jun 2025 13:22:48 GMT

Hello @uvga
Default Power BI line charts don't have a way to show blank data unfortunately, but we can address this by replacing all the blank values with zero in a separate table.

Firstly we create a calendar table to store all the dates between the beginning and end of the sales period:

SalesKPI = CALENDAR(MINX('sales',[Date]),MAXX('sales',[Date]))

Then we create a one to many relationship from the calendar table to the sales table:

Then create a Measure that calculates the total for values that exist, and replace values that don't with 0, like this:

Value = IF(ISBLANK(SUM('products stats'[GetItNow])),0,SUM('products stats'[GetItNow]))

This value can then be used to visualize the total trend over time with blank values appearing as zero.

Here's a demo.

Hope this helps.

Re: Power KPI Matrix

uvga — Tue, 17 Jun 2025 13:30:27 GMT

Thank you for the suggestion!

Unfortunately, I'm using a Live connection to a SQL data source, and Power BI does not allow creating calculated tables or modifying data models in this mode. This means I can't create a separate table with zeros in place of blanks as suggested.

Re: Power KPI Matrix

uvga — Tue, 17 Jun 2025 13:35:44 GMT

It would be extremely helpful if Power KPI Matrix included an option to control how missing values are treated—such as converting blanks to zero or choosing to break the line—directly within the visual.

For reference, the custom visual "Sparkline by OKViz" does offer this kind of behavior—it allows control over whether missing values are shown as blanks or zeros. Unfortunately, it only supports a single measure per visual, so it doesn't meet my need to display multiple lines in one graph (like Sales Qty and End Balance Qty together).

Adding this functionality to Power KPI Matrix would really enhance its flexibility and accuracy, especially for mixed time series with irregular data.

Re: Power KPI Matrix

Nasif_Azam — Wed, 18 Jun 2025 14:42:36 GMT

Hey @uvga ,

You are currently using Power KPI Matrix to visualize two measures as sparklines along a date axis: End Balance Quantity and Sales Quantity. While this works well for End Balance Quantity, which typically has values on most dates, I’m experiencing a visual inaccuracy with Sales Quantity, where many dates have no data (i.e., zero or no sales occurred).

I propose the following improvements to handle missing values more effectively and enhance visual accuracy:

1. Option to Control Missing Data Behavior

Add a configuration setting to allow users to choose how the visual treats missing values:

Display as zero (ideal for sparse transaction data)
Leave as blank
Break the line (avoid interpolating across gaps)

2. Support for Mixed Chart Types (Combo Sparklines)

Enable users to apply different visual styles for different measures within the same matrix:

Line for End Balance Quantity
Column/Bar for Sales Quantity

This would functionally create a combo chart inside the KPI Matrix, improving the interpretability of measures with different temporal patterns.

For Detaled Inforamtion:

Sparkline not showing correctly in Power KPI Matrix

Flat sparkline if no data exists
Create sparklines in a table or matrix in Power BI

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam