Median per category of summarized Table / Remove Outliers from Data

[Michella]

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:

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

Outcome is this:

Can anyone solve this? Thanks!

[v-jingzhang]

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.

[Michella]

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

[v-jingzhang]

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.