Skip to main content
cancel
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.

Reply
Michella
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:

 

Michella_1-1687262177791.png

 

Michella_2-1687262194674.png

 

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:

Michella_3-1687262245805.png

 

Michella_4-1687262259287.png

Can anyone solve this? Thanks!

3 REPLIES 3
v-jingzhang
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)

vjingzhang_0-1687417848411.png

 

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)

vjingzhang_1-1687417974215.png

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

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

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.