Calculating median per person per year

[Anonymous]

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!

[Daniel29195 · Accepted solution]

@Anonymous 

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! 🤠

[Anonymous]

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!

[Daniel29195]

@Anonymous 

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 . 

[Anonymous]

@Daniel29195 -- You're correct, my apologies! I had my visualization configured incorrectly, but your code does the trick 😁 Thank you so much!!