topic Re: Average in DAX in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Average-in-DAX/m-p/3078081#M107105 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="395163" data-lia-user-login="Tan_LC" class="lia-mention lia-mention-user">Tan_LC</a>&nbsp;, how does your data look like and the measure that gives wrong results?</P> Mon, 13 Feb 2023 16:01:46 GMT ERD 2023-02-13T16:01:46Z Average in DAX https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Average-in-DAX/m-p/3077879#M107080 <P>Dear all,</P><P>&nbsp;</P><P>In below table, I would like to calculate the Average using DAX and the desired result should be same as "Average in Excel".</P><P>However, when doing calculation in DAX Power BI, the result turns to be abnormal which giving me a single digit number instead of 4 digits numeric (as shown in "Average in DAX").</P><P>Please help.</P><P>&nbsp;</P><TABLE border="1"><TBODY><TR><TD>Line</TD><TD>1: S</TD><TD>2: P</TD><TD>3: M</TD><TD>Average in Excel</TD><TD>Average in DAX</TD></TR><TR><TD>C1</TD><TD>2,012</TD><TD>2,347</TD><TD>2,261</TD><TD>2,207</TD><TD>7.13</TD></TR><TR><TD>C2</TD><TD>1,796</TD><TD>1,939</TD><TD>1,934</TD><TD>1,890</TD><TD>6.76</TD></TR><TR><TD>C3</TD><TD>2,149</TD><TD>2,242</TD><TD>2,216</TD><TD>2,202</TD><TD>8.69</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Thank you.</P><P>&nbsp;</P><P>Regards,</P><P>Lee Cheng</P> Mon, 13 Feb 2023 14:15:36 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Average-in-DAX/m-p/3077879#M107080 Tan_LC 2023-02-13T14:15:36Z Re: Average in DAX https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Average-in-DAX/m-p/3078081#M107105 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="395163" data-lia-user-login="Tan_LC" class="lia-mention lia-mention-user">Tan_LC</a>&nbsp;, how does your data look like and the measure that gives wrong results?</P> Mon, 13 Feb 2023 16:01:46 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Average-in-DAX/m-p/3078081#M107105 ERD 2023-02-13T16:01:46Z Re: Average in DAX https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Average-in-DAX/m-p/3078631#M107152 <P>Hi,&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="395163" data-lia-user-login="Tan_LC" class="lia-mention lia-mention-user">Tan_LC</a>&nbsp;</P> <P>&nbsp;</P> <P><SPAN>You can try the following methods</SPAN><SPAN>.</SPAN></P> <P><SPAN>In the Power Query, Transpose-Unpivot Columns</SPAN></P> <P><img /></P> <P><img /></P> <LI-CODE lang="markup">Column = CALCULATE(AVERAGE('Table'[Value]),ALLEXCEPT('Table','Table'[Line]))</LI-CODE> <P><img /></P> <P>Or</P> <LI-CODE lang="markup">Measure = CALCULATE(AVERAGE('Table'[Value]),ALLEXCEPT('Table','Table'[Line]))</LI-CODE> <P><img /></P> <P>Is this the result you expect?</P> <P>&nbsp;</P> <P>Best Regards,</P> <P>Community Support Team _Charlotte</P> <P>If this post<SPAN>&nbsp;</SPAN><STRONG>helps</STRONG>, then please consider<SPAN>&nbsp;</SPAN><STRONG><EM>Accept it as the solution</EM></STRONG><SPAN>&nbsp;</SPAN>to help the other members find it more quickly.</P> Tue, 14 Feb 2023 02:04:37 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Average-in-DAX/m-p/3078631#M107152 v-zhangti 2023-02-14T02:04:37Z Re: Average in DAX https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Average-in-DAX/m-p/3078836#M107174 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="291363" data-lia-user-login="ERD" class="lia-mention lia-mention-user">ERD</a>&nbsp;</P><P>I extracted some of the data and share with you as below:</P><P>In the Part ID column, each ID is an unique ID and each row represents 1 count.</P><P>In the DAX Average calculation, it should reflect correct Ave in accordance with the filter I select regardless of Line, Part ID, Shift or Date.&nbsp;</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Line</TD><TD>S</TD><TD>P</TD><TD>M</TD><TD>Ave in Excel</TD></TR><TR><TD>C1</TD><TD>24</TD><TD>17</TD><TD>25</TD><TD>22</TD></TR><TR><TD>C2</TD><TD>10</TD><TD>20</TD><TD>18</TD><TD>16</TD></TR><TR><TD>C3</TD><TD>15</TD><TD>21</TD><TD>10</TD><TD>15</TD></TR></TBODY></TABLE><P>&nbsp;</P><TABLE><TBODY><TR><TD>Machine<BR />No.</TD><TD>Line</TD><TD>Part No.</TD><TD>Part ID</TD><TD>Shift</TD><TD>Date</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32D5</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32D6</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32D7</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32D8</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32DH</TD><TD>S</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32DI</TD><TD>S</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32DJ</TD><TD>S</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32DK</TD><TD>S</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32EE</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32EF</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32EM</TD><TD>P</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32EO</TD><TD>P</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32ES</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32ET</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32EU</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32HB</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32HC</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32HD</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32HE</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32HL</TD><TD>M</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32HM</TD><TD>M</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF135</TD><TD>32HN</TD><TD>M</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D0W</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D0X</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D0Y</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D0Z</TD><TD>S</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D10</TD><TD>S</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D11</TD><TD>S</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D12</TD><TD>S</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D13</TD><TD>S</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D1B</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D1P</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D1Q</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D1R</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D1S</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D1T</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D1U</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D20</TD><TD>P</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D21</TD><TD>P</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D22</TD><TD>P</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D23</TD><TD>P</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D7J</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D7M</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D7N</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D7O</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D7P</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D7Q</TD><TD>M</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D7R</TD><TD>M</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D7X</TD><TD>M</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>HF237</TD><TD>0D7Y</TD><TD>M</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>LN005</TD><TD>29I3</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>LN005</TD><TD>29I4</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>LN005</TD><TD>29I5</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>LN005</TD><TD>29ID</TD><TD>S</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>LN005</TD><TD>29IE</TD><TD>S</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>LN005</TD><TD>29IK</TD><TD>S</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>LN005</TD><TD>29IL</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>LN005</TD><TD>29NX</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>LN005</TD><TD>29NY</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>LN005</TD><TD>29NZ</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>LN005</TD><TD>29O0</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>LN005</TD><TD>29O1</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>LN005</TD><TD>29OB</TD><TD>M</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>LN005</TD><TD>29OC</TD><TD>M</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>LN005</TD><TD>29OD</TD><TD>M</TD><TD>13/2/2023</TD></TR><TR><TD>101</TD><TD>C1</TD><TD>LN005</TD><TD>29OE</TD><TD>M</TD><TD>13/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00T8</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00T9</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00TA</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00TB</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00TC</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00TD</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00TJ</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00TK</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00TL</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00TM</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00U1</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00U2</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00U3</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00UP</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00UQ</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00UR</TD><TD>P</TD><TD>13/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00V6</TD><TD>P</TD><TD>13/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00V7</TD><TD>P</TD><TD>13/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00V8</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00V9</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00W5</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00W6</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00W7</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00W8</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00W9</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00WA</TD><TD>M</TD><TD>13/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00WB</TD><TD>M</TD><TD>13/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00WC</TD><TD>M</TD><TD>13/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00WD</TD><TD>M</TD><TD>13/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>HF328</TD><TD>00WE</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>LN003</TD><TD>2MQB</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>LN003</TD><TD>2MQC</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>LN003</TD><TD>2MQD</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>LN003</TD><TD>2MQE</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>LN003</TD><TD>2MQF</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>LN003</TD><TD>2MQG</TD><TD>P</TD><TD>13/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>LN003</TD><TD>2MQH</TD><TD>P</TD><TD>13/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>LN003</TD><TD>2MQI</TD><TD>P</TD><TD>13/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>LN003</TD><TD>2MQU</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>LN003</TD><TD>2MQV</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>LN003</TD><TD>2MT8</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>LN003</TD><TD>2MT9</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>LN003</TD><TD>2MTA</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>LN003</TD><TD>2MTG</TD><TD>M</TD><TD>13/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>LN003</TD><TD>2MTH</TD><TD>M</TD><TD>13/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>LN003</TD><TD>2MTS</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>LN003</TD><TD>2MTT</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>201</TD><TD>C2</TD><TD>LN003</TD><TD>2MTU</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P44</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P45</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P46</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P47</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P48</TD><TD>S</TD><TD>13/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P49</TD><TD>S</TD><TD>13/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P4A</TD><TD>S</TD><TD>13/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P4B</TD><TD>S</TD><TD>13/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P4C</TD><TD>S</TD><TD>13/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P4D</TD><TD>S</TD><TD>13/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P4E</TD><TD>S</TD><TD>13/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P4F</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P4G</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P4H</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P4I</TD><TD>S</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P5Y</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P5Z</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>TP60</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>TP61</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P86</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P87</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P88</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P8H</TD><TD>M</TD><TD>13/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P8I</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB003</TD><TD>1P8J</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB084</TD><TD>233J</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB084</TD><TD>233K</TD><TD>P</TD><TD>13/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB084</TD><TD>233L</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB084</TD><TD>233M</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB084</TD><TD>235F</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB084</TD><TD>235G</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB084</TD><TD>235L</TD><TD>M</TD><TD>13/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB084</TD><TD>235N</TD><TD>M</TD><TD>13/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB084</TD><TD>235O</TD><TD>M</TD><TD>13/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB084</TD><TD>235P</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB084</TD><TD>235R</TD><TD>M</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB095</TD><TD>0R66</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB095</TD><TD>0R67</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB095</TD><TD>0R6C</TD><TD>P</TD><TD>13/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB095</TD><TD>0R6K</TD><TD>P</TD><TD>13/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB095</TD><TD>0R6L</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB095</TD><TD>0R6M</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB095</TD><TD>0R6N</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB095</TD><TD>0R6O</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB095</TD><TD>0R6Y</TD><TD>P</TD><TD>12/2/2023</TD></TR><TR><TD>301</TD><TD>C3</TD><TD>AB095</TD><TD>0R6Z</TD><TD>P</TD><TD>12/2/2023</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Thanks.</P> Tue, 14 Feb 2023 05:42:18 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Average-in-DAX/m-p/3078836#M107174 Tan_LC 2023-02-14T05:42:18Z Re: Average in DAX https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Average-in-DAX/m-p/3078839#M107175 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="332147" data-lia-user-login="v-zhangti" class="lia-mention lia-mention-user">v-zhangti</a>&nbsp;</P><P>Thanks on the solution given but it still giving me the incorrect outcome. Perhaps, you can have a look on the raw data I reply to&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="291363" data-lia-user-login="ERD" class="lia-mention lia-mention-user">ERD</a>&nbsp; for details.</P><P>Thanks.</P> Tue, 14 Feb 2023 05:44:08 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Average-in-DAX/m-p/3078839#M107175 Tan_LC 2023-02-14T05:44:08Z Re: Average in DAX https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Average-in-DAX/m-p/3078922#M107185 <P>do not really get your point, just guess</P> <P>=AVERAGEX(SUMMARIZE('Table3','Table3'[Line],'Table3'[Shift]),CALCULATE(DISTINCTCOUNT(Table3[Part ID])))</P> Tue, 14 Feb 2023 06:57:06 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Average-in-DAX/m-p/3078922#M107185 wdx223_Daniel 2023-02-14T06:57:06Z Re: Average in DAX https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Average-in-DAX/m-p/3080576#M107343 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="332147" data-lia-user-login="v-zhangti" class="lia-mention lia-mention-user">v-zhangti</a>&nbsp;</P><P>&nbsp;</P><P>In below data, would appreciate if you can advise on the formula to get the Ave using DAX for Line.</P><P>I've tried on your earlier formula, if there are new columns such as Date, Machine No and &amp; Part No added, the Ave result becomes inaccurate.&nbsp;</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Date</TD><TD>Line</TD><TD>Shift</TD><TD>Machine No.</TD><TD>Part No.</TD><TD>Output</TD></TR><TR><TD>12/2/2023</TD><TD>C1</TD><TD>M</TD><TD>101</TD><TD>HF135</TD><TD>4</TD></TR><TR><TD>12/2/2023</TD><TD>C1</TD><TD>M</TD><TD>101</TD><TD>HF237</TD><TD>5</TD></TR><TR><TD>12/2/2023</TD><TD>C1</TD><TD>M</TD><TD>101</TD><TD>LN005</TD><TD>5</TD></TR><TR><TD>12/2/2023</TD><TD>C1</TD><TD>P</TD><TD>101</TD><TD>HF135</TD><TD>5</TD></TR><TR><TD>12/2/2023</TD><TD>C1</TD><TD>P</TD><TD>101</TD><TD>HF237</TD><TD>6</TD></TR><TR><TD>12/2/2023</TD><TD>C1</TD><TD>S</TD><TD>101</TD><TD>HF135</TD><TD>4</TD></TR><TR><TD>12/2/2023</TD><TD>C1</TD><TD>S</TD><TD>101</TD><TD>HF237</TD><TD>4</TD></TR><TR><TD>12/2/2023</TD><TD>C1</TD><TD>S</TD><TD>101</TD><TD>LN005</TD><TD>4</TD></TR><TR><TD>13/2/2023</TD><TD>C1</TD><TD>M</TD><TD>101</TD><TD>HF135</TD><TD>3</TD></TR><TR><TD>13/2/2023</TD><TD>C1</TD><TD>M</TD><TD>101</TD><TD>HF237</TD><TD>4</TD></TR><TR><TD>13/2/2023</TD><TD>C1</TD><TD>M</TD><TD>101</TD><TD>LN005</TD><TD>4</TD></TR><TR><TD>13/2/2023</TD><TD>C1</TD><TD>P</TD><TD>101</TD><TD>HF135</TD><TD>2</TD></TR><TR><TD>13/2/2023</TD><TD>C1</TD><TD>P</TD><TD>101</TD><TD>HF237</TD><TD>4</TD></TR><TR><TD>13/2/2023</TD><TD>C1</TD><TD>S</TD><TD>101</TD><TD>LN005</TD><TD>3</TD></TR><TR><TD>13/2/2023</TD><TD>C1</TD><TD>S</TD><TD>101</TD><TD>HF135</TD><TD>4</TD></TR><TR><TD>13/2/2023</TD><TD>C1</TD><TD>S</TD><TD>101</TD><TD>HF237</TD><TD>5</TD></TR><TR><TD>12/2/2023</TD><TD>C2</TD><TD>M</TD><TD>201</TD><TD>HF328</TD><TD>6</TD></TR><TR><TD>12/2/2023</TD><TD>C2</TD><TD>M</TD><TD>201</TD><TD>LN003</TD><TD>6</TD></TR><TR><TD>12/2/2023</TD><TD>C2</TD><TD>P</TD><TD>201</TD><TD>HF328</TD><TD>7</TD></TR><TR><TD>12/2/2023</TD><TD>C2</TD><TD>P</TD><TD>201</TD><TD>LN003</TD><TD>7</TD></TR><TR><TD>12/2/2023</TD><TD>C2</TD><TD>S</TD><TD>201</TD><TD>HF328</TD><TD>10</TD></TR><TR><TD>13/2/2023</TD><TD>C2</TD><TD>M</TD><TD>201</TD><TD>LN003</TD><TD>2</TD></TR><TR><TD>13/2/2023</TD><TD>C2</TD><TD>M</TD><TD>201</TD><TD>HF328</TD><TD>4</TD></TR><TR><TD>13/2/2023</TD><TD>C2</TD><TD>P</TD><TD>201</TD><TD>HF328</TD><TD>3</TD></TR><TR><TD>13/2/2023</TD><TD>C2</TD><TD>P</TD><TD>201</TD><TD>LN003</TD><TD>3</TD></TR><TR><TD>12/2/2023</TD><TD>C3</TD><TD>M</TD><TD>301</TD><TD>AB003</TD><TD>2</TD></TR><TR><TD>12/2/2023</TD><TD>C3</TD><TD>M</TD><TD>301</TD><TD>AB084</TD><TD>4</TD></TR><TR><TD>12/2/2023</TD><TD>C3</TD><TD>P</TD><TD>301</TD><TD>AB084</TD><TD>3</TD></TR><TR><TD>12/2/2023</TD><TD>C3</TD><TD>P</TD><TD>301</TD><TD>AB003</TD><TD>7</TD></TR><TR><TD>12/2/2023</TD><TD>C3</TD><TD>P</TD><TD>301</TD><TD>AB095</TD><TD>8</TD></TR><TR><TD>12/2/2023</TD><TD>C3</TD><TD>S</TD><TD>301</TD><TD>AB003</TD><TD>8</TD></TR><TR><TD>13/2/2023</TD><TD>C3</TD><TD>M</TD><TD>301</TD><TD>AB003</TD><TD>1</TD></TR><TR><TD>13/2/2023</TD><TD>C3</TD><TD>M</TD><TD>301</TD><TD>AB084</TD><TD>3</TD></TR><TR><TD>13/2/2023</TD><TD>C3</TD><TD>P</TD><TD>301</TD><TD>AB084</TD><TD>1</TD></TR><TR><TD>13/2/2023</TD><TD>C3</TD><TD>P</TD><TD>301</TD><TD>AB095</TD><TD>2</TD></TR><TR><TD>13/2/2023</TD><TD>C3</TD><TD>S</TD><TD>301</TD><TD>AB003</TD><TD>7</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>Thanks.</P> Wed, 15 Feb 2023 02:22:28 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Average-in-DAX/m-p/3080576#M107343 Tan_LC 2023-02-15T02:22:28Z Re: Average in DAX https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Average-in-DAX/m-p/3083037#M107560 <P>Hi,&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="395163" data-lia-user-login="Tan_LC" class="lia-mention lia-mention-user">Tan_LC</a>&nbsp;</P> <P>&nbsp;</P> <P><SPAN>You can try the following methods</SPAN><SPAN>.</SPAN></P> <LI-CODE lang="markup">Measure = Var _N1=CALCULATE(COUNT('Table'[Line]),ALLEXCEPT('Table','Table'[Shift],'Table'[Line])) Var _N2=CALCULATE(DISTINCTCOUNT('Table'[Shift])) Return DIVIDE(_N1,_N2)</LI-CODE> <P><img /></P> <P><img /></P> <P>Is this the result you expect?</P> <P>&nbsp;</P> <P>Best Regards,</P> <P>Community Support Team _Charlotte</P> <P>If this post<SPAN>&nbsp;</SPAN><STRONG>helps</STRONG>, then please consider<SPAN>&nbsp;</SPAN><STRONG><EM>Accept it as the solution</EM></STRONG><SPAN>&nbsp;</SPAN>to help the other members find it more quickly.</P> Thu, 16 Feb 2023 09:23:01 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Average-in-DAX/m-p/3083037#M107560 v-zhangti 2023-02-16T09:23:01Z