cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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:

FILTER(
"TotalFuel", [TotalFuel]
),
[TotalFuel] <> 0
)

Outcome is this:

Can anyone solve this? Thanks!

3 REPLIES 3
Community Support

Hi @Anonymous

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.

Anonymous
Not applicable

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

Community Support

Hi @Anonymous

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.