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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

@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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Chandramouli_BI
Helper III
Helper III

I have created a scatter plot & created 25th,50th,75th &100th percentiles for both x & y axes. So now my data is into 16 grids. I have created calculated column which assigns each row a grid value based on the percentile values. The problem I am facing is that in my dashboard any filters or slicers are applied the percentile line values are not changing. For ex I have a month slicer if I select particular month the percentile line values not changing & also grid slicer(which I have enabled to filter the data) is also not working properly

Chandramouli_BI
Helper III
Helper III

I have created a scatter plot & created 25th,50th,75th &100th percentiles for both x & y axes. So now my data is into 16 grids. I have created calculated column which assigns each row a grid value based on the percentile values. The problem I am facing is that in my dashboard any filters or slicers are applied the percentile line values are not changing. For ex I have a month slicer if I select particular month the percentile line values not changing & also grid slicer(which I have enabled to filter the data) is also not working properly

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
Super User

@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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.