topic Re: How to I Create Quartiles based on Different Groupings? in Desktop
https://community.powerbi.com/t5/Desktop/How-to-I-Create-Quartiles-based-on-Different-Groupings/m-p/1910142#M730937
<P>I've also tried:</P><LI-CODE lang="markup">Quartile1 =
CALCULATE(
PERCENTILE.INC(dataset[rate], 0.25),
ALLEXCEPT(dataset, dataset[Time], dataset[Class2], dataset[Subclass2]))</LI-CODE><P> It seems to be calculating quartiles, but it's not grouping it the way I want (quartiles of rates for each Time, Class, Subclass)</P>Sun, 20 Jun 2021 00:34:30 GMTbrabby_dabby2021-06-20T00:34:30ZHow to I Create Quartiles based on Different Groupings?
https://community.powerbi.com/t5/Desktop/How-to-I-Create-Quartiles-based-on-Different-Groupings/m-p/1904547#M729450
<P>Hello,</P><P>I have a dataset that looks like this:</P><TABLE border="1"><TBODY><TR><TD>Time</TD><TD>Class1</TD><TD>Class2</TD><TD>Subclass1</TD><TD>Subclass2</TD><TD>Vendor</TD><TD>SKU</TD><TD>Rate</TD></TR><TR><TD><P>12</P></TD><TD>1</TD><TD>REF</TD><TD>11</TD><TD>FDR</TD><TD>A</TD><TD>111</TD><TD>.089</TD></TR><TR><TD>12</TD><TD>1</TD><TD>REF</TD><TD>11</TD><TD>FDR</TD><TD>A</TD><TD>112</TD><TD>.112</TD></TR><TR><TD>12</TD><TD>1</TD><TD>REF</TD><TD>11</TD><TD>FDR</TD><TD>A</TD><TD>113</TD><TD>.121</TD></TR><TR><TD>12</TD><TD>1</TD><TD>REF</TD><TD>11</TD><TD>FDR</TD><TD>B</TD><TD>121</TD><TD>.080</TD></TR><TR><TD>12</TD><TD>1</TD><TD>REF</TD><TD>11</TD><TD>FDR</TD><TD>B</TD><TD>122</TD><TD>.065</TD></TR><TR><TD>12</TD><TD>1</TD><TD>REF</TD><TD>11</TD><TD>FDR</TD><TD>C</TD><TD>131</TD><TD>.050</TD></TR><TR><TD>12</TD><TD>1</TD><TD>REF</TD><TD>12</TD><TD>A</TD><TD>A</TD><TD>141</TD><TD>.112</TD></TR><TR><TD>12</TD><TD>1</TD><TD>REF</TD><TD>12</TD><TD>A</TD><TD>B</TD><TD>151</TD><TD><P>.131</P></TD></TR><TR><TD>12</TD><TD>1</TD><TD>REF</TD><TD>12</TD><TD>A</TD><TD>C</TD><TD>161</TD><TD>.054</TD></TR></TBODY></TABLE><P>I have a lot more Times, Classes, Subclasses, SKUs, and Vendors.</P><P> </P><P>First, I want to calculate the first and third quartiles:</P><P>I did so by creating a measure by the following:</P><P> </P><LI-CODE lang="markup">Quartile1 = SUMMARIZE(dataset, dataset[Class1],dataset[Class2],dataset[Subclass1], dataset[Subclass2],"Q1", PERCENTILE.INC(dataset[rate],0.25))</LI-CODE><P> </P><P> </P><P>But I get the following error: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."</P><P>I've tried similar measures also, but when it works, it's not grouping the Quartiles like I want it to.</P><P> </P><P>The Ideal end result is to make a table that looks like this for all the different Times, Classes, and Subclasses:</P><TABLE border="1"><TBODY><TR><TD>Time</TD><TD>Class1</TD><TD>Class2</TD><TD>Subclass1</TD><TD>Subclass2</TD><TD>Vendor</TD><TD>SKU</TD><TD>Rate</TD><TD>Quartile1</TD><TD>Quartile3</TD><TD>IQR</TD><TD>Lower Limit</TD><TD>Upper Limit</TD></TR><TR><TD>12</TD><TD>1</TD><TD>REF</TD><TD>11</TD><TD>FDR</TD><TD>A</TD><TD>111</TD><TD>.089</TD><TD>.30</TD><TD>.45</TD><TD>.15</TD><TD><P>.075</P></TD><TD>.675</TD></TR><TR><TD>24</TD><TD>1</TD><TD>REF</TD><TD>11</TD><TD>FDR</TD><TD>A</TD><TD>111</TD><TD>.099</TD><TD>.41</TD><TD>.51</TD><TD>.10</TD><TD>.26</TD><TD>.66</TD></TR><TR><TD>36</TD><TD>1</TD><TD>REF</TD><TD>11</TD><TD>FDR</TD><TD>A</TD><TD>111</TD><TD>.124</TD><TD>.43</TD><TD>.60</TD><TD>.17</TD><TD>.175</TD><TD>.855</TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE><P>where</P><P>IQR = Quartile1 - Quartile3</P><P>Lower_Limit = Quartile1 - (1.5*IQR) and</P><P>Upper_Limit = Quartile3 + (1.5*IQR)</P><P> </P><P>I would like to dynamically calculate the Quartiles for the different Time, Classes, and Subclasses.</P><P> </P><P>My end goal is to look at a particular SKU and identify whether or not that SKU is below the Lower_Limit or above the Upper_Limit.</P><P> </P><P>Thank you for your help. I am not as familiar with DAX and have been stuck on this for a while.</P>Wed, 16 Jun 2021 18:25:54 GMThttps://community.powerbi.com/t5/Desktop/How-to-I-Create-Quartiles-based-on-Different-Groupings/m-p/1904547#M729450brabby_dabby2021-06-16T18:25:54ZRe: How to I Create Quartiles based on Different Groupings?
https://community.powerbi.com/t5/Desktop/How-to-I-Create-Quartiles-based-on-Different-Groupings/m-p/1910142#M730937
<P>I've also tried:</P><LI-CODE lang="markup">Quartile1 =
CALCULATE(
PERCENTILE.INC(dataset[rate], 0.25),
ALLEXCEPT(dataset, dataset[Time], dataset[Class2], dataset[Subclass2]))</LI-CODE><P> It seems to be calculating quartiles, but it's not grouping it the way I want (quartiles of rates for each Time, Class, Subclass)</P>Sun, 20 Jun 2021 00:34:30 GMThttps://community.powerbi.com/t5/Desktop/How-to-I-Create-Quartiles-based-on-Different-Groupings/m-p/1910142#M730937brabby_dabby2021-06-20T00:34:30ZRe: How to I Create Quartiles based on Different Groupings?
https://community.powerbi.com/t5/Desktop/How-to-I-Create-Quartiles-based-on-Different-Groupings/m-p/1911642#M731356
<P>Hi <LI-USER uid="256378"></LI-USER> ,</P>
<P> </P>
<P>Not sure if I understand you correctly, but if you want a table you will need to use New Table feature.</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="1.PNG" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/537383i967AF742AB62176C/image-size/medium?v=v2&px=400" role="button" title="1.PNG" alt="1.PNG" /></span></P>
<P>And you will get the below result if you are using the first formula.</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="2.PNG" style="width: 353px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/537384i4BD8936F77B53D7A/image-size/medium?v=v2&px=400" role="button" title="2.PNG" alt="2.PNG" /></span></P>
<P> </P>
<P>Best Regards,</P>
<P>Jay</P>
<P> </P>Mon, 21 Jun 2021 08:30:40 GMThttps://community.powerbi.com/t5/Desktop/How-to-I-Create-Quartiles-based-on-Different-Groupings/m-p/1911642#M731356v-jayw-msft2021-06-21T08:30:40Z