topic Need to calculate weighted average based on derived values. in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-to-calculate-weighted-average-based-on-derived-values/m-p/1482944#M28339 <P>Hi All,</P><P>There is a scenario as:&nbsp;</P><TABLE><TBODY><TR><TD>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Input Data</TD></TR><TR><TD>Question category</TD><TD>Question</TD><TD>Rating</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>1</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>1</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>1</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>2</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>2</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>2</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>3</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>3</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>3</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>3</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>0</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>0</TD></TR><TR><TD>A1</TD><TD>Q2</TD><TD>1</TD></TR><TR><TD>A1</TD><TD>Q2</TD><TD>1</TD></TR><TR><TD>A1</TD><TD>Q2</TD><TD>2</TD></TR><TR><TD>A1</TD><TD>Q2</TD><TD>2</TD></TR><TR><TD>A1</TD><TD>Q2</TD><TD>2</TD></TR><TR><TD>A1</TD><TD>Q2</TD><TD>2</TD></TR><TR><TD>A1</TD><TD>Q2</TD><TD>3</TD></TR><TR><TD>A2</TD><TD>Q1</TD><TD>3</TD></TR><TR><TD>A2</TD><TD>Q1</TD><TD>3</TD></TR><TR><TD>A2</TD><TD>Q1</TD><TD>3</TD></TR><TR><TD>A2</TD><TD>Q1</TD><TD>4</TD></TR><TR><TD>A2</TD><TD>Q1</TD><TD>4</TD></TR><TR><TD>A2</TD><TD>Q1</TD><TD>4</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>0</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>0</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>1</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>1</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>2</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>2</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>2</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>2</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Calculation and output will be like below:</P><TABLE><TBODY><TR><TD>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Output Calculation</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>Rating</TD><TD>Weight</TD><TD>Calculation</TD><TD>Final Output</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>1</TD><TD>3</TD><TD>1*3=3</TD><TD>&nbsp;</TD><TD>3+6+12/10&nbsp;</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>2</TD><TD>3</TD><TD>2*3=6</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>3</TD><TD>4</TD><TD>3*4=12</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>0</TD><TD>0</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>A1</TD><TD>Q2</TD><TD>1</TD><TD>2</TD><TD>1*2=2</TD><TD>&nbsp;</TD><TD>2+8+3/7</TD></TR><TR><TD>A1</TD><TD>Q2</TD><TD>2</TD><TD>4</TD><TD>2*4=8</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>A1</TD><TD>Q2</TD><TD>3</TD><TD>1</TD><TD>3*1=3</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>A2</TD><TD>Q1</TD><TD>3</TD><TD>3</TD><TD>3*3=9</TD><TD>&nbsp;</TD><TD>9+12/6</TD></TR><TR><TD>A2</TD><TD>Q1</TD><TD>4</TD><TD>3</TD><TD>4*3=12</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>1</TD><TD>2</TD><TD>1*2=2</TD><TD>&nbsp;</TD><TD>2+8/6</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>2</TD><TD>4</TD><TD>2*4=8</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR></TBODY></TABLE> Mon, 09 Nov 2020 14:48:20 GMT kirti_agarwal28 2020-11-09T14:48:20Z Need to calculate weighted average based on derived values. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-to-calculate-weighted-average-based-on-derived-values/m-p/1482944#M28339 <P>Hi All,</P><P>There is a scenario as:&nbsp;</P><TABLE><TBODY><TR><TD>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Input Data</TD></TR><TR><TD>Question category</TD><TD>Question</TD><TD>Rating</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>1</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>1</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>1</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>2</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>2</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>2</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>3</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>3</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>3</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>3</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>0</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>0</TD></TR><TR><TD>A1</TD><TD>Q2</TD><TD>1</TD></TR><TR><TD>A1</TD><TD>Q2</TD><TD>1</TD></TR><TR><TD>A1</TD><TD>Q2</TD><TD>2</TD></TR><TR><TD>A1</TD><TD>Q2</TD><TD>2</TD></TR><TR><TD>A1</TD><TD>Q2</TD><TD>2</TD></TR><TR><TD>A1</TD><TD>Q2</TD><TD>2</TD></TR><TR><TD>A1</TD><TD>Q2</TD><TD>3</TD></TR><TR><TD>A2</TD><TD>Q1</TD><TD>3</TD></TR><TR><TD>A2</TD><TD>Q1</TD><TD>3</TD></TR><TR><TD>A2</TD><TD>Q1</TD><TD>3</TD></TR><TR><TD>A2</TD><TD>Q1</TD><TD>4</TD></TR><TR><TD>A2</TD><TD>Q1</TD><TD>4</TD></TR><TR><TD>A2</TD><TD>Q1</TD><TD>4</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>0</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>0</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>1</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>1</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>2</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>2</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>2</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>2</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Calculation and output will be like below:</P><TABLE><TBODY><TR><TD>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Output Calculation</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>Rating</TD><TD>Weight</TD><TD>Calculation</TD><TD>Final Output</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>1</TD><TD>3</TD><TD>1*3=3</TD><TD>&nbsp;</TD><TD>3+6+12/10&nbsp;</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>2</TD><TD>3</TD><TD>2*3=6</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>3</TD><TD>4</TD><TD>3*4=12</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>A1</TD><TD>Q1</TD><TD>0</TD><TD>0</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>A1</TD><TD>Q2</TD><TD>1</TD><TD>2</TD><TD>1*2=2</TD><TD>&nbsp;</TD><TD>2+8+3/7</TD></TR><TR><TD>A1</TD><TD>Q2</TD><TD>2</TD><TD>4</TD><TD>2*4=8</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>A1</TD><TD>Q2</TD><TD>3</TD><TD>1</TD><TD>3*1=3</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>A2</TD><TD>Q1</TD><TD>3</TD><TD>3</TD><TD>3*3=9</TD><TD>&nbsp;</TD><TD>9+12/6</TD></TR><TR><TD>A2</TD><TD>Q1</TD><TD>4</TD><TD>3</TD><TD>4*3=12</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>1</TD><TD>2</TD><TD>1*2=2</TD><TD>&nbsp;</TD><TD>2+8/6</TD></TR><TR><TD>A2</TD><TD>Q2</TD><TD>2</TD><TD>4</TD><TD>2*4=8</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR></TBODY></TABLE> Mon, 09 Nov 2020 14:48:20 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-to-calculate-weighted-average-based-on-derived-values/m-p/1482944#M28339 kirti_agarwal28 2020-11-09T14:48:20Z Re: Need to calculate weighted average based on derived values. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-to-calculate-weighted-average-based-on-derived-values/m-p/1482951#M28340 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="262182" data-lia-user-login="kirti_agarwal28" class="lia-mention lia-mention-user">kirti_agarwal28</a> , Try a measure like </P> <P>divide(calculate(sum(Table[Rating]), filter(Table, Table[Rating]&gt;0)),calculate(count(Table[Rating]), filter(Table, Table[Rating]&gt;0)))</P> Mon, 09 Nov 2020 15:06:10 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-to-calculate-weighted-average-based-on-derived-values/m-p/1482951#M28340 amitchandak 2020-11-09T15:06:10Z