Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
th3h0bb5
Resolver II
Resolver II

Percentile varies between DAX and Reference Line?

I have data about how fast cars are going. I want to show the 99th Percentile of max speed. I've done this two ways. In DAX with the PERCENTILE.INC() formula:

DAX Example.png

 

And with an Analysis reference line:

Analysis Example.png

But they return different results! One is 198.72 and the other is 199.96

 

Why? I've tried .INC and .EXC for the DAX side, but neither matches the reference line.

 

 

8 REPLIES 8
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi  @th3h0bb5,

 

As I know, the PERCENTILE.INC() function has a little different from Percentile line in Analytics pane.

 

The fucntion is calculated for each row of a table and the Percentile line is used for analysis the chart.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Cherry,

 

But how exactly would that impact the final results? The Tabular view has Car | Max Speed | Calculation. The Visualization has Car | Max Speed | Calculation. If the input is the same, what is PowerBI doing that causes it to produce different results?

 

Cheers,


Hobbs

Hi @th3h0bb5,

 

Sorry for the previous reply. 

 

By my tests again on the version Power BI Desktop 2.60.5169.3201 64-bit (July 2018), it seems that the value of PERCENTILE.INC() is the same with the value of Percentile Line.

 

Untitled.png

 

You could have a reference of my attachment.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hmm. Interesting. I wonder if perhaps it's something in my data that is causing the issue. Perhaps one handles NULLS differently than the other?

Hi @th3h0bb5,

 

Have you solved your problem?

 

If you have solved, could you please share the solution or always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If you still need help, could you share a dummy pbix file which can reproduce the issue, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello Cherry

 

Unfortunately no this is not a solution. I have tested the Percentile.Inc and Percentile.Exc DAX functions against a set of data and neither of the values returned match the Percentile (or Median) analytical line values. It appears the analytical line values are being calculated in some way because the values returned are not in the background data set.

 

I'm more than happy to share screen shots of my charts as well as my DAX function return values. 

Anonymous
Not applicable

I am seeing the same behavior in a scatter chart.  I created measures using both PERCENTILE.EXC and PERCENTILE.INC for P10, P50 and P90.  I also applied the same percentage lines to the scatter chart, which sometimes match and sometimes do not.  The "Filter and slicers affecting this visual" are the same in both the scatter chart and cards containing the measure.  This does not make sense and certainly is an issue if trying to show the P-values to users while also plotting a line on the chart that doesn't align.

I've attached a sample file replicating the problem, based on DIAD data.

 

https://1drv.ms/u/s!ArTScuQPiZBjgegWE0E2nPELKu8sjQ

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors