topic Re: Table with conditional quartiles in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Table-with-conditional-quartiles/m-p/4116009#M163408 <P>i have a table with discounts for countries. I want to calculate the quartile 1 of the region for the specific product and then the quartile 1 of all the countries regardless of region based on the product.&nbsp;</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Country</TD><TD>Region</TD><TD>Product</TD><TD>Country Discount</TD><TD>Q1 Region</TD><TD>Q1 Overall</TD></TR><TR><TD>China</TD><TD>Eastern Asia</TD><TD>A</TD><TD>0.54</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Korea</TD><TD>Eastern Asia</TD><TD>B</TD><TD>0.52</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Japan</TD><TD>Eastern Asia</TD><TD>C</TD><TD>0.51</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Taiwan</TD><TD>Eastern Asia</TD><TD>A</TD><TD>0.62</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Slovenia</TD><TD>Southern Europe</TD><TD>B</TD><TD>0.69</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Hungary</TD><TD>Eastern Europe</TD><TD>C</TD><TD>0.55</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Czech Republic</TD><TD>Eastern Europe</TD><TD>A</TD><TD>0.49</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Poland</TD><TD>Eastern Europe</TD><TD>B</TD><TD>0.43</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Slovakia</TD><TD>Eastern Europe</TD><TD>C</TD><TD>0.53</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Romania</TD><TD>Eastern Europe</TD><TD>A</TD><TD>0.7</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Bulgaria</TD><TD>Eastern Europe</TD><TD>B</TD><TD>0.66</TD></TR></TBODY></TABLE> Fri, 23 Aug 2024 15:23:44 GMT Sammy22 2024-08-23T15:23:44Z Table with conditional quartiles https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Table-with-conditional-quartiles/m-p/4115650#M163377 <P>i have a table with discounts for countries. I want to calculate the quartile 1 of the region for the specific product and then the quartile 1 of all the countries regardless of region based on the product.&nbsp;</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Country</TD><TD>Region</TD><TD>Product</TD><TD>Country Discount</TD><TD>Q1 Region</TD><TD>Q1 Overall</TD></TR><TR><TD>China</TD><TD>Eastern Asia</TD><TD>A</TD><TD>0.54</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Korea</TD><TD>Eastern Asia</TD><TD>B</TD><TD>0.52</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Japan</TD><TD>Eastern Asia</TD><TD>C</TD><TD>0.51</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Taiwan</TD><TD>Eastern Asia</TD><TD>A</TD><TD>0.62</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Slovenia</TD><TD>Southern Europe</TD><TD>B</TD><TD>0.69</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Hungary</TD><TD>Eastern Europe</TD><TD>C</TD><TD>0.55</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Czech Republic</TD><TD>Eastern Europe</TD><TD>A</TD><TD>0.49</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Poland</TD><TD>Eastern Europe</TD><TD>B</TD><TD>0.43</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Slovakia</TD><TD>Eastern Europe</TD><TD>C</TD><TD>0.53</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Romania</TD><TD>Eastern Europe</TD><TD>A</TD><TD>0.7</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Bulgaria</TD><TD>Eastern Europe</TD><TD>B</TD><TD>0.66</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR></TBODY></TABLE> Fri, 23 Aug 2024 11:58:48 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Table-with-conditional-quartiles/m-p/4115650#M163377 Sammy22 2024-08-23T11:58:48Z Re: Table with conditional quartiles https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Table-with-conditional-quartiles/m-p/4116009#M163408 <P>i have a table with discounts for countries. I want to calculate the quartile 1 of the region for the specific product and then the quartile 1 of all the countries regardless of region based on the product.&nbsp;</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Country</TD><TD>Region</TD><TD>Product</TD><TD>Country Discount</TD><TD>Q1 Region</TD><TD>Q1 Overall</TD></TR><TR><TD>China</TD><TD>Eastern Asia</TD><TD>A</TD><TD>0.54</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Korea</TD><TD>Eastern Asia</TD><TD>B</TD><TD>0.52</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Japan</TD><TD>Eastern Asia</TD><TD>C</TD><TD>0.51</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Taiwan</TD><TD>Eastern Asia</TD><TD>A</TD><TD>0.62</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Slovenia</TD><TD>Southern Europe</TD><TD>B</TD><TD>0.69</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Hungary</TD><TD>Eastern Europe</TD><TD>C</TD><TD>0.55</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Czech Republic</TD><TD>Eastern Europe</TD><TD>A</TD><TD>0.49</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Poland</TD><TD>Eastern Europe</TD><TD>B</TD><TD>0.43</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Slovakia</TD><TD>Eastern Europe</TD><TD>C</TD><TD>0.53</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Romania</TD><TD>Eastern Europe</TD><TD>A</TD><TD>0.7</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>Bulgaria</TD><TD>Eastern Europe</TD><TD>B</TD><TD>0.66</TD></TR></TBODY></TABLE> Fri, 23 Aug 2024 15:23:44 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Table-with-conditional-quartiles/m-p/4116009#M163408 Sammy22 2024-08-23T15:23:44Z Re: Table with conditional quartiles https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Table-with-conditional-quartiles/m-p/4116020#M163395 <P>You would use PERCENTILEX for that.&nbsp; Have you tried that?</P> <P>&nbsp;</P> <P>Note that your data is insufficient for this. You need many more datapoints.</P> Fri, 23 Aug 2024 15:32:00 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Table-with-conditional-quartiles/m-p/4116020#M163395 lbendlin 2024-08-23T15:32:00Z Re: Table with conditional quartiles https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Table-with-conditional-quartiles/m-p/4116372#M163415 <P>Managed to find a solution. This is calculating the 75th percentile:</P><P>&nbsp;</P><DIV><DIV><SPAN>75</SPAN><SPAN>th Percentile Discount% =</SPAN> <SPAN>calculate</SPAN><SPAN>(</SPAN><SPAN>PERCENTILE.INC</SPAN><SPAN>(</SPAN><SPAN>'Table'</SPAN><SPAN>[Country_Discount%]</SPAN><SPAN> ,</SPAN><SPAN>0.75</SPAN><SPAN>),</SPAN><SPAN>ALLEXCEPT</SPAN><SPAN>(</SPAN><SPAN>'Table'</SPAN><SPAN>,</SPAN><SPAN>Table</SPAN><SPAN>[PRODUCT]</SPAN><SPAN>))</SPAN></DIV></DIV> Fri, 23 Aug 2024 18:51:19 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Table-with-conditional-quartiles/m-p/4116372#M163415 Sammy22 2024-08-23T18:51:19Z Re: Table with conditional quartiles https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Table-with-conditional-quartiles/m-p/4117768#M163474 <P>Thanks&nbsp;lbendlin&nbsp;for the helpful thoughts.<BR /><BR /></P> <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="687121" data-lia-user-login="Sammy22" class="lia-mention lia-mention-user">Sammy22</a>&nbsp;,</P> <P>&nbsp;</P> <P>I'm going to make some additions to yours, here I've created two calculated columns:</P> <LI-CODE lang="markup">Q1 Region = CALCULATE( PERCENTILE.INC('Table'[Country Discount], 0.25), ALLEXCEPT('Table', 'Table'[Region],'Table'[Product])) Q1 Overall = CALCULATE( PERCENTILE.INC('Table'[Country Discount],0.25), ALLEXCEPT('Table','Table'[Product])) </LI-CODE> <P>The result is as follows:</P> <P><img /></P> <P>&nbsp;</P> <P>Best Regards,<BR />Zhu<BR />Community Support Team</P> <P>&nbsp;</P> <P>If there is any post&nbsp;<STRONG><EM>helps</EM></STRONG>, then please consider&nbsp;<STRONG><EM>Accept it as the solution</EM></STRONG>&nbsp;&nbsp;to help the other members find it more quickly.<BR />If I misunderstand your needs or you still have problems on it, please feel free to let us know.&nbsp;<STRONG><EM>Thanks a lot!</EM></STRONG></P> <P>&nbsp;</P> Mon, 26 Aug 2024 05:07:06 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Table-with-conditional-quartiles/m-p/4117768#M163474 Anonymous 2024-08-26T05:07:06Z