Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
How do I get the median of filtered values.
Year | Phase | study_id | name | category |
2017 | Phase 1 | abc | A | 1 |
2017 | Phase 1 | ab | A | 1 |
2017 | Phase O | bc | B | 1 |
2017 | Phase O | abcd | C | 1 |
2018 | Phase 1 | bcd | A | 1 |
2018 | Phase O | be | A | 2 |
2018 | Phase 1 | ef | B | 1 |
2018 | Phase O | en | C | 1 |
2018 | Phase 1 | zf | B | 1 |
I want to calculate median of count of study_id by Year, Phase on name. Also take into account for the filter being used in the report of Category = 1
Result should be
Year | Phase | Median of count of study_id |
2017 | Phase 1 | 1 |
Solved! Go to Solution.
I was able to solve it myself.
medianx(values(count(name))), study_id))
Hi @bharukc ,
I can't understand your scenario exactly, could you share me more details?
1. Like what @BeaBF mentioned, what's the meaning of "count of study_id by Year, Phase on name"? also by name?
2. Based on my understanding, the count for 2017 Phase is 2. Then how does the result "1" get?
Year | Phase | Median of count of study_id |
2017 | Phase 1 | 1 |
Could you explain the calculation logic?
Best Regards,
Icey
I was able to solve it myself.
medianx(values(count(name))), study_id))
Hi @bharukc ,
Glad to hear that. Please accept your reply as solution. Your contribution is highly appreciated.
Best Regards,
Icey
Hi:
There's a few ways to do this. Here I added a calculated column to the table (I named it Study). This gets the Category 1 piece.
I hope this is what you are looking for..
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
22 | |
17 | |
17 | |
11 | |
7 |
User | Count |
---|---|
27 | |
26 | |
13 | |
12 | |
12 |