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

The 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.

Reply
FarmerJason
Frequent Visitor

Calculating median per person per year

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:

numberuser_idresolved_at
1000001abc1231/1/2024
1000002def4561/1/2024
1000003abc1231/2/2024
1000004ghi7891/3/2024
1000005def4561/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!

1 ACCEPTED SOLUTION
Daniel29195
Super User
Super User

@FarmerJason 

output

Daniel29195_0-1707321680253.png

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

 

View solution in original post

4 REPLIES 4
Daniel29195
Super User
Super User

@FarmerJason 

output

Daniel29195_0-1707321680253.png

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!

@FarmerJason 

just to check my logic with you : 
below is sample data that im using 

Daniel29195_0-1707323289022.png

 

the median for 2024 shoud be 4 as also shown below . am i correct ? 

Daniel29195_1-1707323399325.png

 

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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