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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I
Helper I

Median per category of summarized Table / Remove Outliers from Data

Hi everyone, I need advice please. I am trying to remove outliers from my data. I have a dim table (dimsailing) and a fact table (shipreadings).

I want to show the aggregated metric per itinerary but without the outliers data:






In the end I also want to calculate the avg, min, max metric per itinerary.

The rule to filter out an an outlier is:

  • must be more than 4 sailingkeys per itinerary

  • Status = closed

  • metric < half of median per itinerary

In the shipreadings table I have Sailingkey, Status and metric, the itinerary comes from the dimsailing. I have used the summarize function to do so:


    SUMMARIZE('shipreadings', shipreadings[SailingKey], 'dimsailing'[itinerary], dimsailing[SailingStatus], 
        "TotalFuel", [TotalFuel]
    [TotalFuel] <> 0



Outcome is this:




Can anyone solve this? Thanks!

Community Support
Community Support

Hi @Michella 


You can create the following calculated columns in your summarize table:

Half Median of Metric = CALCULATE(MEDIAN('Table'[metric]),ALLEXCEPT('Table','Table'[itinerary]))/2

Count of SailingKey = CALCULATE(COUNT('Table'[SailingKey]),ALLEXCEPT('Table','Table'[itinerary]))

Outlier = IF('Table'[Count of SailingKey]>4 && 'Table'[Status]="Closed" && 'Table'[metric]<'Table'[Half Median of Metric], 1, 0)



Then create a measure and display it in a table visual along with itinerary. 

Total metric without outliers = CALCULATE(SUM('Table'[metric]),'Table'[Outlier]=0)


Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Hi @v-jingzhang,

I appreciate your help! I have the problem, that the sailingkey and the fuel comes from a fact table where each sailingkey is only once, but the itinerary comes from a dimtable, where the sailingkeys and itinerary are not unique.  Any advice on that? Unfortuneatley I am unable to share my file since it contains sensitive data.


Kind regards, Michella

Hi @Michella 


Can you create some dummy data to show the structure and relationships of tables? You can add them into my sample pbix file and share it through a link for download.

Helpful resources


Fabric certifications survey

Certification feedback opportunity for the community.


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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