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.
Hello!
I need help with calculating a measure (median) on a per-person-per-year basis.
I have 3 tables:
Incidents – fact table, has column called [number] with unique values and another called [user_id] with non-unique values and another called [resolved_at] with non-unique values
Users – dimension table, has column called [sys_id] with unique values
Dates – dimension table, has Date column with unique values
Relationships:
Incidents Users
user_id *:1 sys_id
Incidents Dates
resolved_at *:1 date
I need to visualize the median number of incidents per user, per year. We can assume that all Users have been active for the entirety of the period covered by Dates.
So for this data:
number | user_id | resolved_at |
1000001 | abc123 | 1/1/2024 |
1000002 | def456 | 1/1/2024 |
1000003 | abc123 | 1/2/2024 |
1000004 | ghi789 | 1/3/2024 |
1000005 | def456 | 1/3/2024 |
....we would expect to get a value of 2 incidents per user per year for the median (values of 1, 2, 2 for the three users).
Any help is appreciated. Thank you in advance!
Solved! Go to Solution.
output
median per year per user =
var ds =
MEDIANX(
ADDCOLUMNS(
SUMMARIZE(
'incidents',
'users'[user_id],
'dimdate'[year]
),
"x" , CALCULATE(COUNT('incidents'[number]))
),
[x]
)
return ds
let me know if it works for you ,
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
output
median per year per user =
var ds =
MEDIANX(
ADDCOLUMNS(
SUMMARIZE(
'incidents',
'users'[user_id],
'dimdate'[year]
),
"x" , CALCULATE(COUNT('incidents'[number]))
),
[x]
)
return ds
let me know if it works for you ,
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
Hi @Daniel29195 , thank you for the response. I should've been more clear: I'm looking for a measure which I can plot against time, so that I get a single value for each year in my dataset.... "Median number of incidents per user for 2023 is....."
Hope that makes sense, thank you again for your help!
just to check my logic with you :
below is sample data that im using
the median for 2024 shoud be 4 as also shown below . am i correct ?
if yes, then the measure should work .
if not, then i think i didnt understand the requirement, and it would be helpful to show an example to get the desired output .
@Daniel29195 -- You're correct, my apologies! I had my visualization configured incorrectly, but your code does the trick 😁 Thank you so much!!
User | Count |
---|---|
141 | |
70 | |
69 | |
53 | |
52 |
User | Count |
---|---|
208 | |
94 | |
64 | |
60 | |
57 |