Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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
Solved! Go to Solution.
You can create a What-if parameter to control the percentile.
1. Create a What-if parameter:
2. Set the percentile:
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:
---------------------------------------------------
Not filtered:
Proud to be a Super User!
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
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
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!
You can create a What-if parameter to control the percentile.
1. Create a What-if parameter:
2. Set the percentile:
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:
---------------------------------------------------
Not filtered:
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |