Summary Table (SPC) showing NANs in columns Upper, Lower 99% Limit and Upper, Lower 95% Limit

[F_Reh]

Good afternoon,

I am using a Summary Table (SPC), where there are valid values for the Columns Value, Numerator, Denominator and Centerline.

However, matters gets "pear-shaped" in the following columns (which are displaying NAN):

1) Upper 99% Limit.

2) Upper 95% Limit.

3) Lower 95% Limit.

4) Lower 99% Limit.

 I already tried creating a measure so that 

Denominator_Sum = IF(SUM('TableX'[Denominator]) = 0, blank(),SUM('TableX'[Denominator]))

But this did not rectify the issue. Kindly advise what else I need to do.

Regards

[jaineshp · Accepted solution]

Hey @F_Reh,

Looking at your SPC Summary Table with NAN values in the control limit columns, here are the key steps to resolve this:

Check Your Base Calculations:

• Verify your centerline calculation is producing valid numbers (not zero or negative for certain control charts)
• Ensure your subgroup sizes are consistent and greater than 1
• Check if you have sufficient data points (typically need at least 20+ subgroups)

Handle Division by Zero:

• Create measures for control limits that check for valid denominators:

Upper_95_Limit =
IF(
OR([Centerline] = 0, [Centerline] = BLANK(), [StdDev] = 0),
BLANK(),
[Centerline] + (1.96 * [StdDev])
)

Common SPC Issues:

• For P-charts: ensure proportion values are between 0 and 1
• For C-charts: verify count data is non-negative integers
• For X-bar charts: check subgroup standard deviation calculations

Quick Fixes:

• Replace your denominator measure with error handling for ALL control limit calculations
• Add IFERROR() wrapper around your limit formulas
• Verify your chart type matches your data structure (attribute vs variable data)

The NAN typically appears when there's a mathematical impossibility in the control limit formula - usually division by zero or square root of negative numbers.

Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

Best Regards,
Jainesh Poojara | Power BI Developer

[Anonymous]

Hi @F_Reh,

Thanks for reaching out to the Microsoft fabric community forum.

The issue you’re seeing with NaN values in the control limit columns is most likely caused by invalid proportion values in your dataset. Also you have already ruled out some of the common culprits (like Numerator > Denominator and division errors), which is great. Based on the logic in SPC calculations, particularly for control limits, the issue is likely happening at the square root stage, not during the initial division. As @DataNinja777 and @jaineshp have already responded to your query, kindly go through their responses and check if your issyue can be resolved.

I would also take a moment to thank @DataNinja777 and @jaineshp, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[jaineshp]

Hey @Anonymous,

Thanks for the recognition! Contributing to the community and seeing others benefit from shared knowledge is what makes these forums so valuable.

Best Regards,
Jainesh Poojara | Power BI Developer

[F_Reh]

I added an additional column using this formula IsError = ISERROR(DIVIDE([Numerator_Sum],[Denominator_Sum])) but there are no errors:

So I am not quite certain where all the NANs are coming from.

[F_Reh]

Here is chronological list of the Numerator values (no negatives):

Here is chronological list of the Denominator values (no negatives - but only some NULLs which in Power Query I changed to 0's anyway):

There doesn't seem to be instances of any Numerator being greater than the corresponding Denominator(s):

Kind Regards

[DataNinja777 · Accepted solution]

Hi @F_Reh ,

The 'NAN' (Not a Number) values in your control limit columns are very likely the result of a mathematical error, specifically taking the square root of a negative number. This is a common issue in rate-based SPC charts. The standard formula for these limits is typically Control Limit = Centerline ± Z * sqrt(p(1-p)/n), where p is your Value (Numerator / Denominator). The expression inside the square root, p(1-p), becomes negative whenever the rate p is greater than 1. This means the calculation fails and produces NAN on any row where your Numerator is greater than your Denominator. Your attempt to fix a zero denominator was a good step, but this particular error points to an invalid rate value instead.

The best solution is to validate your source data. A rate or proportion cannot be greater than 100%, so a Numerator larger than its Denominator indicates a fundamental problem with how the data is being recorded or measured. Correcting this at the source is the most reliable fix.

If you cannot correct the data, you should adjust your DAX measures to handle this scenario gracefully. You can prevent the error by adding a condition that checks if the rate is valid before attempting the square root calculation. Below is a sample of a robust measure for your Upper 95% Limit.

Upper 95% Limit =
VAR p = [Value]  -- Or DIVIDE(SUM('TableX'[Numerator]), SUM('TableX'[Denominator]))
VAR n = SUM('TableX'[Denominator])
VAR Centerline = [Centerline]
VAR Z_Score = 1.96

-- Check for the condition that causes a negative under the square root
IF(
    p > 1 OR p < 0,
    BLANK(), -- Return blank if the rate is invalid, preventing the NAN error
    Centerline + Z_Score * SQRT( p * (1 - p) / n )
)

This DAX code first checks if the rate p is outside the valid range of 0 to 1. If it is, the formula returns BLANK(), which will show up as an empty cell instead of NAN. The actual control limit calculation only proceeds if the rate is valid. You can apply this same logic to your other three limit measures, adjusting the Z_Score and changing the + to a - for the lower limits, to resolve all the NAN errors in your table.

Best regards,

[jaineshp · Accepted solution]

Hey @F_Reh,

Looking at your SPC Summary Table with NAN values in the control limit columns, here are the key steps to resolve this:

Check Your Base Calculations:

• Verify your centerline calculation is producing valid numbers (not zero or negative for certain control charts)
• Ensure your subgroup sizes are consistent and greater than 1
• Check if you have sufficient data points (typically need at least 20+ subgroups)

Handle Division by Zero:

• Create measures for control limits that check for valid denominators:

Upper_95_Limit =
IF(
OR([Centerline] = 0, [Centerline] = BLANK(), [StdDev] = 0),
BLANK(),
[Centerline] + (1.96 * [StdDev])
)

Common SPC Issues:

• For P-charts: ensure proportion values are between 0 and 1
• For C-charts: verify count data is non-negative integers
• For X-bar charts: check subgroup standard deviation calculations

Quick Fixes:

• Replace your denominator measure with error handling for ALL control limit calculations
• Add IFERROR() wrapper around your limit formulas
• Verify your chart type matches your data structure (attribute vs variable data)

The NAN typically appears when there's a mathematical impossibility in the control limit formula - usually division by zero or square root of negative numbers.

Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

Best Regards,
Jainesh Poojara | Power BI Developer