PERCENTILE.EXC
A
C
- CALCULATE
- CALCULATETABLE
- CALENDAR
- CALENDARAUTO
- CEILING
- CHISQ.DIST
- CHISQ.DIST.RT
- CHISQ.INV
- CHISQ.INV.RT
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- COALESCE
- COLUMNSTATISTICS
- COMBIN
- COMBINA
- COMBINEVALUES
- CONCATENATE
- CONCATENATEX
- CONFIDENCE.NORM
- CONFIDENCE.T
- CONTAINS
- CONTAINSROW
- CONTAINSSTRING
- CONTAINSSTRINGEXACT
- CONVERT
- COS
- COSH
- COT
- COTH
- COUNT
- COUNTA
- COUNTAX
- COUNTBLANK
- COUNTROWS
- COUNTX
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNCD
- COUPNUM
- COUPPCD
- CROSSFILTER
- CROSSJOIN
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENTGROUP
- CUSTOMDATA
D
E
I
N
O
P
R
S
- SAMEPERIODLASTYEAR
- SAMPLE
- SEARCH
- SECOND
- SELECTCOLUMNS
- SELECTEDMEASURE
- SELECTEDMEASUREFORMATSTRING
- SELECTEDMEASURENAME
- SELECTEDVALUE
- SIGN
- SIN
- SINH
- SLN
- SQRT
- SQRTPI
- STARTOFMONTH
- STARTOFQUARTER
- STARTOFYEAR
- STDEVX.P
- STDEVX.S
- STDEV.P
- STDEV.S
- SUBSTITUTE
- SUBSTITUTEWITHINDEX
- SUM
- SUMMARIZE
- SUMMARIZECOLUMNS
- SUMX
- SWITCH
- SYD
T
U
What is PERCENTILE.EXC?
The PERCENTILE.EXC function in Power BI is a DAX formula used for calculating the k-th percentile for a given set of values. It returns the value below which a given percentage of data falls. The function takes two arguments: the array of values and the k-th percentile to calculate. The k-th percentile is a value that divides the data into two parts: the part below the percentile and the part above it. The PERCENTILE.EXC function excludes the k-th percentile value from the calculation.
Syntax of PERCENTILE.EXC
The syntax of the PERCENTILE.EXC function in Power BI is as follows:
PERCENTILE.EXC(❰column❱, ❰k❱)
The ❰column❱ argument is the column of values for which you want to calculate the k-th percentile. The ❰k❱ argument is the percentile you want to calculate, expressed as a decimal number between 0 and 1.
How to Use PERCENTILE.EXC in Power BI
To use the PERCENTILE.EXC function in Power BI, follow these steps:
1. Open Power BI Desktop and select the data source you want to use.
2. Create a new measure by selecting the ‘New Measure’ button in the ‘Modeling’ tab.
3. In the formula bar, type the following formula:
PERCENTILE.EXC(❰column❱, ❰k❱)
Replace ❰column❱ with the name of the column that contains the values for which you want to calculate the percentile. Replace ❰k❱ with the desired percentile expressed as a decimal number.
4. Press Enter to calculate the measure.
5. Use the measure in a visual, such as a table, matrix, or chart.
Examples of Using PERCENTILE.EXC in Power BI
Suppose you have a sales data table that contains a column named ‘Sales Amount.’ To calculate the 90th percentile of sales, follow these steps:
1. Create a new measure by selecting the ‘New Measure’ button in the ‘Modeling’ tab.
2. In the formula bar, type the following formula:
90th Percentile Sales = PERCENTILE.EXC('Sales Data'[Sales Amount], 0.9)
This formula calculates the 90th percentile of the ‘Sales Amount’ column in the ‘Sales Data’ table.
3. Press Enter to calculate the measure.
4. Use the ’90th Percentile Sales’ measure in a visual, such as a table, matrix, or chart.
Suppose you have a product data table that contains a column named ‘Price.’ To calculate the 75th percentile of product prices by category, follow these steps:
1. Create a new measure by selecting the ‘New Measure’ button in the ‘Modeling’ tab.
2. In the formula bar, type the following formula:
75th Percentile Price =
PERCENTILE.EXC(
FILTER('Product Data', 'Product Data'[Category] = "❰category name❱"),
0.75
)
Replace ❰category name❱ with the name of the category for which you want to calculate the percentile. This formula calculates the 75th percentile of the ‘Price’ column in the ‘Product Data’ table for the specified category.
3. Press Enter to calculate the measure.
4. Use the ’75th Percentile Price’ measure in a visual, such as a table, matrix, or chart.
The PERCENTILE.EXC DAX function in Power BI is a powerful tool for analyzing data. It enables users to calculate the k-th percentile of a set of values, which is useful for identifying trends and patterns in the data. By following the steps outlined in this article, you can use the PERCENTILE.EXC function to analyze data and create insightful visualizations.
