topic Re: How to calculate percentile in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-percentile/m-p/3337577#M125185 <P>Hi,&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="477011" data-lia-user-login="Cevola" class="lia-mention lia-mention-user">Cevola</a>&nbsp;</P> <P>&nbsp;</P> <P>Based on the data you provided, what kind of output do you expect?</P> <P><img /></P> <P>Reference can be made to such formulas.</P> <P><A href="https://learn.microsoft.com/en-us/dax/percentile-exc-function-dax" target="_blank">PERCENTILE.EXC function (DAX) - DAX | Microsoft Learn</A></P> <P><A href="https://learn.microsoft.com/en-us/dax/percentile-inc-function-dax" target="_blank">PERCENTILE.INC function (DAX) - DAX | Microsoft Learn</A></P> <P>&nbsp;</P> <P>Best Regards,</P> <P>Community Support Team _Charlotte</P> <P>If this post<SPAN>&nbsp;<STRONG>helps, then please consider&nbsp;<EM>Accept it as the solution&nbsp;to help the other members find it more quickly.</EM></STRONG></SPAN></P> Wed, 19 Jul 2023 02:22:09 GMT v-zhangti 2023-07-19T02:22:09Z How to calculate percentile https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-percentile/m-p/3334899#M125057 <P>Hi,</P><P>&nbsp;</P><P>I need to create a percentile column based on the VALUE column. The percentile needs to be calculated only for some IDs like A1, A3, A7, A8 etc. The data i have is:</P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Id</TD><TD>Value</TD><TD>Percentile</TD></TR><TR><TD>A1</TD><TD>2.993857962</TD><TD>&nbsp;</TD></TR><TR><TD>A2</TD><TD>4.790980789</TD><TD>&nbsp;</TD></TR><TR><TD>A3</TD><TD>5.285423474</TD><TD>&nbsp;</TD></TR><TR><TD>A4</TD><TD>9.845946214</TD><TD>&nbsp;</TD></TR><TR><TD>A5</TD><TD>13.1619472</TD><TD>&nbsp;</TD></TR><TR><TD>A6</TD><TD>3.061712719</TD><TD>&nbsp;</TD></TR><TR><TD>A7</TD><TD>14.10853515</TD><TD>&nbsp;</TD></TR><TR><TD>A8</TD><TD>20.40455305</TD><TD>&nbsp;</TD></TR><TR><TD>A9</TD><TD>8.082005991</TD><TD>&nbsp;</TD></TR><TR><TD>A10</TD><TD>0.927973851</TD><TD>&nbsp;</TD></TR><TR><TD>A11</TD><TD>9.357774268</TD><TD>&nbsp;</TD></TR><TR><TD>A12</TD><TD>12.00309514</TD><TD>&nbsp;</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I am using this measure but I am not getting the required output</P><DIV><DIV><SPAN>Percentile = </SPAN></DIV><DIV><SPAN>VAR</SPAN><SPAN> TotalVal = </SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; [Value Total]</SPAN></DIV><DIV><SPAN>RETURN</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>IF</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>HASONEVALUE</SPAN><SPAN>(Data[ID]),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>COALESCE</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>DIVIDE</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>COUNTROWS</SPAN><SPAN>(Data),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>FILTER</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>ALLEXCEPT</SPAN><SPAN>(Data,Data[ID]),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Data[Value] &lt; TotalVal</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>COUNTROWS</SPAN><SPAN>(Data),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>ALLEXCEPT</SPAN><SPAN>(Data,Data[ID])</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>0</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; )</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; )</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>Please help</SPAN></DIV></DIV> Mon, 17 Jul 2023 18:46:19 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-percentile/m-p/3334899#M125057 Cevola 2023-07-17T18:46:19Z Re: How to calculate percentile https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-percentile/m-p/3337577#M125185 <P>Hi,&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="477011" data-lia-user-login="Cevola" class="lia-mention lia-mention-user">Cevola</a>&nbsp;</P> <P>&nbsp;</P> <P>Based on the data you provided, what kind of output do you expect?</P> <P><img /></P> <P>Reference can be made to such formulas.</P> <P><A href="https://learn.microsoft.com/en-us/dax/percentile-exc-function-dax" target="_blank">PERCENTILE.EXC function (DAX) - DAX | Microsoft Learn</A></P> <P><A href="https://learn.microsoft.com/en-us/dax/percentile-inc-function-dax" target="_blank">PERCENTILE.INC function (DAX) - DAX | Microsoft Learn</A></P> <P>&nbsp;</P> <P>Best Regards,</P> <P>Community Support Team _Charlotte</P> <P>If this post<SPAN>&nbsp;<STRONG>helps, then please consider&nbsp;<EM>Accept it as the solution&nbsp;to help the other members find it more quickly.</EM></STRONG></SPAN></P> Wed, 19 Jul 2023 02:22:09 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-calculate-percentile/m-p/3337577#M125185 v-zhangti 2023-07-19T02:22:09Z