topic Display median by day of week in Desktop
https://community.powerbi.com/t5/Desktop/Display-median-by-day-of-week/m-p/2554585#M904033
<P>Hello, I am a new user of Power BI Deskop. I face a relatively simple problem but I can't find an obvious solution... </P><P> </P><P>I have a data set (see example below) with which I want to calculate the median number of entrance per day of the week.</P><P> </P><P>When I make my graphs, the median is not correct because it calculates the values only between 1 and 0.</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Chris_compond_0-1654155433066.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/725655iE0FDE788B4622081/image-size/medium?v=v2&px=400" role="button" title="Chris_compond_0-1654155433066.png" alt="Chris_compond_0-1654155433066.png" /></span></P><P> </P><P>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?</P><P> </P><P>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!</P><P> </P><TABLE border="1"><TBODY><TR><TD>Date</TD><TD>Day_of_week</TD><TD>No.patient</TD><TD>Service</TD><TD>Entrance</TD></TR><TR><TD>03.01.2022</TD><TD>1</TD><TD>1001</TD><TD>ORT</TD><TD>1</TD></TR><TR><TD>03.01.2022</TD><TD>1</TD><TD>1002</TD><TD>CHIR</TD><TD>1</TD></TR><TR><TD>03.01.2022</TD><TD>1</TD><TD>1003</TD><TD>ORT</TD><TD>0</TD></TR><TR><TD>03.01.2022</TD><TD>1</TD><TD>1004</TD><TD>CHIR</TD><TD>0</TD></TR><TR><TD>04.01.2022</TD><TD>2</TD><TD>1005</TD><TD>MED</TD><TD>1</TD></TR><TR><TD>04.01.2022</TD><TD>2</TD><TD>1006</TD><TD>MED</TD><TD>1</TD></TR><TR><TD>04.01.2022</TD><TD>2</TD><TD>1007</TD><TD>URG</TD><TD>1</TD></TR><TR><TD>05.01.2022</TD><TD>3</TD><TD>1008</TD><TD>URG</TD><TD>0</TD></TR><TR><TD>05.01.2022</TD><TD>3</TD><TD>1009</TD><TD>ORT</TD><TD>1</TD></TR><TR><TD>05.01.2022</TD><TD>3</TD><TD>1010</TD><TD>GASTRO</TD><TD>1</TD></TR><TR><TD>05.01.2022</TD><TD>3</TD><TD>1011</TD><TD>GASTRO</TD><TD>1</TD></TR><TR><TD>06.01.2022</TD><TD>4</TD><TD>1012</TD><TD>ORT</TD><TD>0</TD></TR><TR><TD>06.01.2022</TD><TD>4</TD><TD>1013</TD><TD>URG</TD><TD>1</TD></TR><TR><TD>06.01.2022</TD><TD>4</TD><TD>1014</TD><TD>ORT</TD><TD>1</TD></TR><TR><TD>06.01.2022</TD><TD>4</TD><TD>1015</TD><TD>CHIR</TD><TD>1</TD></TR><TR><TD>06.01.2022</TD><TD>4</TD><TD>1016</TD><TD>ORT</TD><TD>0</TD></TR><TR><TD>06.01.2022</TD><TD>4</TD><TD>1017</TD><TD>CHIR</TD><TD>0</TD></TR><TR><TD>07.01.2022</TD><TD>5</TD><TD>1018</TD><TD>MED</TD><TD>1</TD></TR><TR><TD>07.01.2022</TD><TD>5</TD><TD>1019</TD><TD>MED</TD><TD>1</TD></TR><TR><TD>07.01.2022</TD><TD>5</TD><TD>1020</TD><TD>URG</TD><TD>1</TD></TR><TR><TD>07.01.2022</TD><TD>5</TD><TD>1021</TD><TD>URG</TD><TD>0</TD></TR><TR><TD>07.01.2022</TD><TD>5</TD><TD>1022</TD><TD>ORT</TD><TD>1</TD></TR><TR><TD>08.01.2022</TD><TD>6</TD><TD>1023</TD><TD>GASTRO</TD><TD>1</TD></TR><TR><TD>08.01.2022</TD><TD>6</TD><TD>1024</TD><TD>GASTRO</TD><TD>1</TD></TR></TBODY></TABLE>Thu, 02 Jun 2022 07:40:41 GMTChris_compond2022-06-02T07:40:41ZDisplay median by day of week
https://community.powerbi.com/t5/Desktop/Display-median-by-day-of-week/m-p/2554585#M904033
<P>Hello, I am a new user of Power BI Deskop. I face a relatively simple problem but I can't find an obvious solution... </P><P> </P><P>I have a data set (see example below) with which I want to calculate the median number of entrance per day of the week.</P><P> </P><P>When I make my graphs, the median is not correct because it calculates the values only between 1 and 0.</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Chris_compond_0-1654155433066.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/725655iE0FDE788B4622081/image-size/medium?v=v2&px=400" role="button" title="Chris_compond_0-1654155433066.png" alt="Chris_compond_0-1654155433066.png" /></span></P><P> </P><P>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?</P><P> </P><P>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!</P><P> </P><TABLE border="1"><TBODY><TR><TD>Date</TD><TD>Day_of_week</TD><TD>No.patient</TD><TD>Service</TD><TD>Entrance</TD></TR><TR><TD>03.01.2022</TD><TD>1</TD><TD>1001</TD><TD>ORT</TD><TD>1</TD></TR><TR><TD>03.01.2022</TD><TD>1</TD><TD>1002</TD><TD>CHIR</TD><TD>1</TD></TR><TR><TD>03.01.2022</TD><TD>1</TD><TD>1003</TD><TD>ORT</TD><TD>0</TD></TR><TR><TD>03.01.2022</TD><TD>1</TD><TD>1004</TD><TD>CHIR</TD><TD>0</TD></TR><TR><TD>04.01.2022</TD><TD>2</TD><TD>1005</TD><TD>MED</TD><TD>1</TD></TR><TR><TD>04.01.2022</TD><TD>2</TD><TD>1006</TD><TD>MED</TD><TD>1</TD></TR><TR><TD>04.01.2022</TD><TD>2</TD><TD>1007</TD><TD>URG</TD><TD>1</TD></TR><TR><TD>05.01.2022</TD><TD>3</TD><TD>1008</TD><TD>URG</TD><TD>0</TD></TR><TR><TD>05.01.2022</TD><TD>3</TD><TD>1009</TD><TD>ORT</TD><TD>1</TD></TR><TR><TD>05.01.2022</TD><TD>3</TD><TD>1010</TD><TD>GASTRO</TD><TD>1</TD></TR><TR><TD>05.01.2022</TD><TD>3</TD><TD>1011</TD><TD>GASTRO</TD><TD>1</TD></TR><TR><TD>06.01.2022</TD><TD>4</TD><TD>1012</TD><TD>ORT</TD><TD>0</TD></TR><TR><TD>06.01.2022</TD><TD>4</TD><TD>1013</TD><TD>URG</TD><TD>1</TD></TR><TR><TD>06.01.2022</TD><TD>4</TD><TD>1014</TD><TD>ORT</TD><TD>1</TD></TR><TR><TD>06.01.2022</TD><TD>4</TD><TD>1015</TD><TD>CHIR</TD><TD>1</TD></TR><TR><TD>06.01.2022</TD><TD>4</TD><TD>1016</TD><TD>ORT</TD><TD>0</TD></TR><TR><TD>06.01.2022</TD><TD>4</TD><TD>1017</TD><TD>CHIR</TD><TD>0</TD></TR><TR><TD>07.01.2022</TD><TD>5</TD><TD>1018</TD><TD>MED</TD><TD>1</TD></TR><TR><TD>07.01.2022</TD><TD>5</TD><TD>1019</TD><TD>MED</TD><TD>1</TD></TR><TR><TD>07.01.2022</TD><TD>5</TD><TD>1020</TD><TD>URG</TD><TD>1</TD></TR><TR><TD>07.01.2022</TD><TD>5</TD><TD>1021</TD><TD>URG</TD><TD>0</TD></TR><TR><TD>07.01.2022</TD><TD>5</TD><TD>1022</TD><TD>ORT</TD><TD>1</TD></TR><TR><TD>08.01.2022</TD><TD>6</TD><TD>1023</TD><TD>GASTRO</TD><TD>1</TD></TR><TR><TD>08.01.2022</TD><TD>6</TD><TD>1024</TD><TD>GASTRO</TD><TD>1</TD></TR></TBODY></TABLE>Thu, 02 Jun 2022 07:40:41 GMThttps://community.powerbi.com/t5/Desktop/Display-median-by-day-of-week/m-p/2554585#M904033Chris_compond2022-06-02T07:40:41ZRe: Display median by day of week
https://community.powerbi.com/t5/Desktop/Display-median-by-day-of-week/m-p/2559572#M905426
<P>There's a built-in function for that - MEDIANX() - have you tried that?</P>Sun, 05 Jun 2022 12:48:33 GMThttps://community.powerbi.com/t5/Desktop/Display-median-by-day-of-week/m-p/2559572#M905426lbendlin2022-06-05T12:48:33ZRe: Display median by day of week
https://community.powerbi.com/t5/Desktop/Display-median-by-day-of-week/m-p/2559654#M905445
<P>lo</P><P> </P><P>Try this</P><DIV><DIV><SPAN>TEST MEDIAN = </SPAN><SPAN>calculate</SPAN><SPAN>(</SPAN><SPAN>median</SPAN><SPAN>(</SPAN><SPAN>'MEDIAN'[Entrance]</SPAN><SPAN>),</SPAN><SPAN>ALLEXCEPT</SPAN><SPAN>(</SPAN><SPAN>'MEDIAN'</SPAN><SPAN>,</SPAN><SPAN>'MEDIAN'[Day_of_week]</SPAN><SPAN>))</SPAN></DIV></DIV><P> </P><P> </P>Sun, 05 Jun 2022 17:12:22 GMThttps://community.powerbi.com/t5/Desktop/Display-median-by-day-of-week/m-p/2559654#M905445JamesFr062022-06-05T17:12:22ZRe: Display median by day of week
https://community.powerbi.com/t5/Desktop/Display-median-by-day-of-week/m-p/2565560#M907116
<P>Hi <LI-USER uid="396679"></LI-USER> ,</P>
<P>Has your problem been solved? If so, would you like to accept the helpful solution.</P>
<P>If not, could you please show the expected output.</P>
<P><SPAN>Best Regards,</SPAN><BR /><SPAN>Community Support Team _ kalyj</SPAN></P>Wed, 08 Jun 2022 06:01:52 GMThttps://community.powerbi.com/t5/Desktop/Display-median-by-day-of-week/m-p/2565560#M907116v-kalyj-msft2022-06-08T06:01:52Z