Amazon QuickSight – medianIf

The ‘medianIf’ function in Amazon QuickSight is used to compute the median value of a measure, subject to a specified condition.

Syntax

#Start#
medianIf(measure, condition)
#End#

This function takes two arguments:

• measure: This parameter specifies the measure for which the median value is to be computed.

• condition: This parameter specifies a condition that is applied to filter the data before computing the median.

Suppose we have a dataset that contains information about test scores for students and we want to find the median score for students who scored above 80. We can use the medianIf() function in Amazon QuickSight to do this.

To find the median score for students who scored above 80, we can create a new visual in Amazon QuickSight and add a table with the following settings:

• Drag the “Test Score” measure to the Values section.
• Click on the drop-down arrow next to “Test Score” and select “MedianIf”.
• In the “Condition” field, enter “Test Score > 80”.

This will result in a table that shows the median test score for students who scored above 80.

Alternatively, we can create a calculated field in Amazon QuickSight with the following expression:

Example

#Start#
medianIf(TestScore, TestScore > 80)
#End#

This expression will compute the median test score for students who scored above 80.

For instance, if our test score data is as follows:

Then, the median test score for students who scored above 80 will be:

Example

#Start#
medianIf(TestScore, TestScore > 80) = 85
#End#

In this example, we can see that the medianIf() function has computed the median test score for students who scored above 80, resulting in a useful metric that can inform our educational decisions.