Reference line types of median and percentile not calculating correctly

[EHA_SD]

When adding one of the pre-defined reference line of median or percentile type to a chart the lines are not calculating correctly.

They seem to be treating nulls as 0s.

Given the dataset:

You get a median of 5 and a 90th percentile of 49:

When the actual values should be a median of 35 and 90th percentile of 55:

The average, minimum, and maximum lines seem to be calculating correctly.

[v-hashadapu]

Hi @EHA_SD , 

Could you kindly confirm if you have submitted this as an idea in the Ideas Forum? If so, sharing the link here would be beneficial for other community members who may have similar feedback.

Since we haven't received a response in a long time, we are closing this thread. For further discussions or questions, please start a new thread in the Microsoft Fabric Community Forum — we will be happy to assist.

Thank you for being a part of the Microsoft Fabric Community.

[v-hashadapu]

Hi @EHA_SD , Thank you for reaching out to the Microsoft Community Forum.

Please let us know if your issue is solved. If it is, consider marking the answers that helped 'Accept as Solution', so others with similar queries can find them easily. If not, please share the details.
Thank you.

[EHA_SD]

The problem is not resolved. I have updated to version 2.141.1754.0 64-bit (March, 2025) and the issue remains.

I do not expect this problem to be solved until Microsoft fundementally changes the way these non-additive calculations are handled. Likely with an option in the function or the refererence line options to treat nulls as zeros (include) or treat nulls as nulls (exclude).

It just seems bizarre to me that adding a default 'minimum' reference line will return the actual minimum across a series (and not interpret nulls as zero), but for median and percentile suddenly nulls are treated as zeros. If they are going to treat nulls as zero at least be consistent across functions.

Again, I am well aware that there are ways to force DAX calculations to ignore blanks. My issue is with the default reference lines that users may not realize how they are calculating.

Thank-you.

[v-hashadapu]

Hi @EHA_SD , Thank you for reaching out to the Microsoft Community Forum.

You are right, when you turn on “Show items with no data” in Power BI, the default reference lines for median and percentile can give misleading results. That’s because Power BI starts treating blank values as zeroes in those lines, which can throw off the whole calculation. So instead of showing the true median, it might show something way off. This happens because it’s using something like PERCENTILE.INC behind the scenes and it’s not filtering out the blanks like you’d expect. But when you use DAX functions like MEDIAN() or create a measure, blanks are ignored which is how it should work.

If you haven’t already, I’d suggest posting this to the Ideas Forum - MS Fabric Community to help push for a fix. If you get enough votes Microsoft may implement this in future.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[EHA_SD]

Thank-you for your interesting and helpful response. Based on my testing it seems like it is using PERCENTILE.INC with no filters (keeping blanks and treating them as zeros).

90th PERCENTILEX.EXC (no filters) = 57

90th PERCENTILEX.INC (no filters) = 49

90th PERCENTILEX.EXC (filter blank) = ERROR / INVALID (expected behaviour)

90th PERCENTILEX.INC (filter blanks) = 55

AUTO / DEFAULT 90th percentile reference line = 49

Calculation by hand = 55

Excel 90th INC = 55

Excel 90th EXC = #NUM! (expected behaviour)

Essentially, once you turn on the "show items with no data" for the data column the calculations are incorrect for the auto / default median and percentile reference lines.

Comparison using default median reference line:

When you add a raw column to a card and choose the default aggregation of median, it automatically filters the blanks. Same if you create a DAX measure using CALCULATE(MEDIAN([Value])) and put it on a card, or MEDIANX() with filtering out blanks and put it on a card. So the default behaviour in most contexts is to filter the blanks, but for some reason the reference line default is to include them as zeros.

There should still be more information on how those default reference lines are being calculated (or even control over what options to use, EXC vs. INC, include blanks as 0 vs. ignore blanks.). The whole point of these 'default' reference lines is to make it easy, for both DAX experts and non-DAX experts alike, to add additional context to their charts quickly.

[Anonymous]

HI @EHA_SD,

AFAIK, current we can't manually affect these PERCENTILE functions internal calculation processings. Have you tried to add a new column to replace blank to zero and use this new column to calculate?

Regards,

Xiaoxin Sheng

[EHA_SD]

Thank-you for taking the time to reply.

There are lots of solutions out there on how to force DAX to ignore the blanks, my point in posting was to bring attention for other users who may not be as familiar with DAX to the fact that pre-defined / default reference lines may not behave as expected and will treat blanks as zeros in some situations, and to advocate for more control over how these reference lines can be controlled in the formatting pane (or an easier option built directly into the DAX function to make creating our own easier).

There are times when a null is just a legitimate null, and it should not be treated as a zero. For example, maybe the data are missing for a particular week, or maybe an office only operates Monday to Friday (so including Sat/Sun as zeros will skew the measure), or maybe a company only does orders on a bi-monthly basis (so including the other months will skew the measure).

[Anonymous]

HI @EHA_SD,

If you are interesting about Dax handle with blank, perhaps you can check at the following blog:

How to handle BLANK in DAX measures - SQLBI

Regards,

Xiaoxin Sheng

[Deku]

There are two versions of percentile in Dax .Inc and .exc. Inc considers blanks. The default line must use this version. You can create your own measure with the exc version to get the result you want