Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
in sales column
let assume median = $100
all calculations and visualizations will be based on a filter of the whole page: sales > $100*20%
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))
Hi, @Sding
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
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:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
82 | |
65 | |
63 | |
56 |
User | Count |
---|---|
171 | |
111 | |
110 | |
72 | |
71 |