STDEVX and PERCENTILEX.INC ; aside form the math, do they operate differently?

[MarcoMazzocco]

Hi...STDEVX works without a table function while PECENTILEX.INC forces me to use a table function like SUMMARIZECOLUMNS in order to get the correct result. Is there a mechanical difference in how the functions operate? 

[v-sshirivolu]

Hi @MarcoMazzocco ,

Thanks for reaching out to the Microsoft fabric community forum.

STDEVX.P works directly with a table, but PERCENTILEX.INC needs a table expression like VALUES() to function correctly. The attached .pbix file shows this difference using SalesData and three measures.

Doesn't work reliably:

PERCENTILEX.INC(SalesData[Sales], SalesData[Sales], 0.9)
 

Works properly:

PERCENTILEX.INC(VALUES(SalesData[Sales]), SalesData[Sales], 0.9)
 
Please find the below attached .pbix file for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Sreeteja.
Community Support Team

[MarcoMazzocco]

This helps expalin the logic for sure. There seems to be an issue still since I'm using a measure instead of a column as the expression to evaluate. PERCENTILEX.INC(VALUES(table), [Measure],0.9) Does a measure change the behavior? 

[v-sshirivolu]

Hi @MarcoMazzocco 

Absolutely, using a measure directly in PERCENTILEX.INC impacts its behaviour. For instance -

PERCENTILEX.INC(VALUES('Table'), [Measure], 0.9)

In this case, the measure returns the same value for every row because it doesn't respond to the row context created by VALUES(). This leads to incorrect percentile results.

The solution is to wrap the measure in CALCULATE(), ensuring it evaluates per row -

PERCENTILEX.INC(
   VALUES('Table'[Column]),
   CALCULATE([Measure]),
   0.9
)

This approach guarantees the measure recalculates for each row, delivering accurate results.

[v-sshirivolu]

Hi @MarcoMazzocco ,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

[v-sshirivolu]

Hi @MarcoMazzocco ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[v-sshirivolu]

Hi @MarcoMazzocco ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

[bhanu_gautam]

@MarcoMazzocco 

 The mechanical difference lies in the requirement of a table context for PERCENTILEX.INC to evaluate the entire dataset correctly, whereas STDEVX can operate directly on a column or an expression without needing an explicit table context.

DAX
// Using STDEVX directly on a column
StandardDeviation = STDEVX(VALUES(Table[Column]), Table[Column])

// Using PERCENTILEX.INC with SUMMARIZECOLUMNS to provide the correct context
Percentile = PERCENTILEX.INC(SUMMARIZECOLUMNS(Table[Column]), Table[Column], 0.5)

In the PERCENTILEX.INC example, SUMMARIZECOLUMNS is used to create a table context that includes the values from Table[Column], allowing the function to calculate the percentile correctly.

[MarcoMazzocco]

Thanks !!