cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

Anonymous
Not applicable

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors