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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sding
Frequent Visitor

How to filter out buttom 20 percent of the median value of a column

in sales column 

 

let assume median = $100

 

all calculations and visualizations will be based on a filter of the whole page: sales > $100*20%

3 REPLIES 3
Sding
Frequent Visitor

Thanks for replies from both of you. 

However, I have a bit of trouble to get the right result.

I shot the screen, the new measure "Show If Above 20% of Median" shows a result that is the same as the median of the original column. 

I need the new measure  can be used in any situation just like the original "sold_price" column (but get tide of the bottom 20 percent of low rows under current conditions (filters))

 

 

Show If Above 20% of Median =
VAR __median20 =
CALCULATE(
MEDIAN(syd_all_records_add_lga_drop_addr[sold_price]))*0.2
VAR __totalsales =
MEDIAN( syd_all_records_add_lga_drop_addr[sold_price] ) // i did not use sum as it shows sum up of all values but i want show the median figure.
RETURN
IF ( __totalsales > __median20, __totalsales, BLANK () ) // it turns out when * more than 1, the if condition falls all to blank, whereas * less than 1 all falls to the true condition.

20 median.jpg

 

 

v-alq-msft
Community Support
Community Support

Hi, @Sding 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

g1.png

 

You may create a measure as below.

 

Sales measure = 
var _median = 
CALCULATE(
    MEDIAN('Table'[Sales]),
    ALL('Table')
)*0.2
var tab = 
SUMMARIZE(
    'Table',
    'Table'[ID],
    'Table'[Date],
    "Sale",
    IF(
        SUM('Table'[Sales])>_median,
        SUM('Table'[Sales])
    )
)
return
SUMX(
    tab,
    [Sale]
)

 

 

Result:

g3.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

mahoneypat
Employee
Employee

You can add a page filter but you'll have to choose which columns to filter with that.  Another approach is to consider the following, so you will get blanks (auto filtering) whenever the total sales in below that threshold

 

MedianSales = a calculate(median(), ALL() type of measure

 

Show If Above 20% of Median =
VAR __median20 = [MedianSales] * 0.2
VAR __totalsales =
SUM ( Sales[Amount] ) // replace with actual table[column]
RETURN
IF ( __totalsales >= __median20, __totalsales, BLANK () )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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