cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chris_compond
New Member

Display median by day of week

Hello, I am a new user of Power BI Deskop.  I face a relatively simple problem but I can't find an obvious solution... 

 

I have a data set (see example below) with which I want to calculate the median number of entrance per day of the week.

 

When I make my graphs, the median is not correct because it calculates the values only between 1 and 0.

Chris_compond_0-1654155433066.png

 

What is the best solution? Should I create different calculated tables with aggregated value by day of the week? should I make a  measure with a DAX formula?

 

I have tried to use the "summarize" function without success. I also tried to creat a calulated table with the "group by" function but I am afraid that it will not be efficient when I have very large data sets. Thanks a lot for your help!

 

DateDay_of_weekNo.patientServiceEntrance
03.01.202211001ORT1
03.01.202211002CHIR1
03.01.202211003ORT0
03.01.202211004CHIR0
04.01.202221005MED1
04.01.202221006MED1
04.01.202221007URG1
05.01.202231008URG0
05.01.202231009ORT1
05.01.202231010GASTRO1
05.01.202231011GASTRO1
06.01.202241012ORT0
06.01.202241013URG1
06.01.202241014ORT1
06.01.202241015CHIR1
06.01.202241016ORT0
06.01.202241017CHIR0
07.01.202251018MED1
07.01.202251019MED1
07.01.202251020URG1
07.01.202251021URG0
07.01.202251022ORT1
08.01.202261023GASTRO1
08.01.202261024GASTRO1
3 REPLIES 3
v-kalyj-msft
Community Support
Community Support

Hi @Chris_compond ,

Has your problem been solved? If so, would you like to accept the helpful solution.

If not, could you please show the expected output.

Best Regards,
Community Support Team _ kalyj

JamesFr06
Super User
Super User

lo

 

Try this

TEST MEDIAN = calculate(median('MEDIAN'[Entrance]),ALLEXCEPT('MEDIAN','MEDIAN'[Day_of_week]))

 

 

lbendlin
Super User
Super User

There's a built-in function for that - MEDIANX() - have you tried that?

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10

The Power BI Community Show

Join us on October 3 at 11 am PST when Amit Chandak, a Power BI Super User, will demo how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors