cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gsraje
Regular Visitor

Need to create measure that calculates percentile

Hi, I'm trying to create a measure that will calculate the percentile based on a measure value. I have slicers based on which the measure value changes. The percentile value should change dynamically with the slicers applied. I tried to use the percentilex.inc function but is giving incorrect results. Can anyone suggest me approach for solving the issue.

 

I have calculated 5th percentile in a column which gives values as expected but does not change with slicers. I need to create a percentile measure which will change dynamically with slicer selection. 

 

The calculated column is

5th percentile correct = 
var temp = 
    SUMMARIZE(
        Sheet1,
        Sheet1[ID],
        "max value single column",
        [Max Value Single Measure]
    )
return
    PERCENTILEX.INC(
        temp,
        [max value single column],
        0.05
    )

 

The 5th percentile created using a measure is giving incorrect results and also is not dynamic.

 

gsraje_0-1623817275117.png

 

Can anyone please suggest how do I write percentile measure which would change with slicer selection and give correct results. 

 

This is the power bi file

https://drive.google.com/file/d/1d-eS40GPGilrB1cN2BdAdVIn3oIQAmaO/view?usp=sharing

1 ACCEPTED SOLUTION
DataInsights
Super User II
Super User II

@gsraje,

 

You can create a What-if parameter to control the percentile.

 

1. Create a What-if parameter:

 

DataInsights_0-1623963899710.png

 

DataInsights_1-1623963910354.png

 

2. Set the percentile:

 

DataInsights_4-1623964401089.png

 

 

3. Create measure:

 

5th percentile measure = 
VAR vTableID =
    CALCULATETABLE ( VALUES ( Sheet1[ID] ), ALLSELECTED ( Sheet1 ) )
VAR vTableWithMeasure =
    ADDCOLUMNS ( vTableID, "max value single column", [Max Value Single Measure] )
VAR vResult =
    PERCENTILEX.INC (
        vTableWithMeasure,
        [max value single column],
        [Percentile Value]
    )
RETURN
    vResult

The measure [Percentile Value] is automatically created when you create the What-if parameter.

 

4. Display the measure in a visual. Adding it to a table visual as shown in your example produces incorrect results due to the measure being sliced by other columns in the table visual. It works correctly in a card, however, as shown below.

 

Filtered:

 

DataInsights_2-1623964268021.png

---------------------------------------------------

Not filtered:

 

DataInsights_5-1623964558022.png

 

View solution in original post

2 REPLIES 2
gsraje
Regular Visitor

Hi @DataInsights ,

 

Thank you so much. It worked for me. Although, without creating parameter and passing in value directly works as well. Thanks a lot!

DataInsights
Super User II
Super User II

@gsraje,

 

You can create a What-if parameter to control the percentile.

 

1. Create a What-if parameter:

 

DataInsights_0-1623963899710.png

 

DataInsights_1-1623963910354.png

 

2. Set the percentile:

 

DataInsights_4-1623964401089.png

 

 

3. Create measure:

 

5th percentile measure = 
VAR vTableID =
    CALCULATETABLE ( VALUES ( Sheet1[ID] ), ALLSELECTED ( Sheet1 ) )
VAR vTableWithMeasure =
    ADDCOLUMNS ( vTableID, "max value single column", [Max Value Single Measure] )
VAR vResult =
    PERCENTILEX.INC (
        vTableWithMeasure,
        [max value single column],
        [Percentile Value]
    )
RETURN
    vResult

The measure [Percentile Value] is automatically created when you create the What-if parameter.

 

4. Display the measure in a visual. Adding it to a table visual as shown in your example produces incorrect results due to the measure being sliced by other columns in the table visual. It works correctly in a card, however, as shown below.

 

Filtered:

 

DataInsights_2-1623964268021.png

---------------------------------------------------

Not filtered:

 

DataInsights_5-1623964558022.png

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors