topic Re: How to create an average of a category of a items from a table with a list of data of different in Desktop
https://community.powerbi.com/t5/Desktop/How-to-create-an-average-of-a-category-of-a-items-from-a-table/m-p/1536061#M630301
<P><LI-USER uid="270132"></LI-USER> I have looked at the Excel file, but don't know which values you wish to use for x, y, z. What should the value of x be exactly? Which column are you wanting to average? Have you tried: </P>
<P> </P>
<P>Result = AVERAGEX(FILTER(Table, Table[Column] <> 0), Table[Column])</P>Mon, 07 Dec 2020 09:09:27 GMTAllisonKennedy2020-12-07T09:09:27ZHow to create an average of a category of a items from a table with a list of data of different cate
https://community.powerbi.com/t5/Desktop/How-to-create-an-average-of-a-category-of-a-items-from-a-table/m-p/1534548#M629908
<P>How to create an average of a category of a items from a table with a list of data of different categories?</P><P> </P><P>Hi folks,</P><P> </P><P>I have a table with following sample data:</P><P> </P><P>Shop_City Shop City Nos Nos Wgt Fruits/Schezwan</P><TABLE><TBODY><TR><TD>CostCo_New York</TD><TD>CostCo</TD><TD>New York</TD><TD>0</TD><TD>0</TD><TD>0%</TD></TR><TR><TD>CostCo_Washington</TD><TD>CostCo</TD><TD>Washington</TD><TD>23</TD><TD>1</TD><TD>192%</TD></TR><TR><TD>CostCo_Chicago</TD><TD>CostCo</TD><TD>Chicago</TD><TD>11</TD><TD>1</TD><TD>32%</TD></TR><TR><TD>CostCo_Marin</TD><TD>CostCo</TD><TD>Marin</TD><TD>23</TD><TD>1</TD><TD>2300%</TD></TR><TR><TD>CostCo_Houston</TD><TD>CostCo</TD><TD>Houston</TD><TD>12</TD><TD>1</TD><TD>600%</TD></TR><TR><TD>CostCo_New York</TD><TD>CostCo</TD><TD>New York</TD><TD>0</TD><TD>0</TD><TD>0%</TD></TR><TR><TD>CostCo_Washington</TD><TD>CostCo</TD><TD>Washington</TD><TD>12</TD><TD>1</TD><TD>100%</TD></TR><TR><TD>CostCo_Chicago</TD><TD>CostCo</TD><TD>Chicago</TD><TD>34</TD><TD>1</TD><TD>100%</TD></TR><TR><TD>CostCo_Marin</TD><TD>CostCo</TD><TD>Marin</TD><TD>0</TD><TD>0</TD><TD>0%</TD></TR><TR><TD>CostCo_Houston</TD><TD>CostCo</TD><TD>Houston</TD><TD>12</TD><TD>1</TD><TD>600%</TD></TR><TR><TD>CostCo_New York</TD><TD>CostCo</TD><TD>New York</TD><TD>24</TD><TD>1</TD><TD>100%</TD></TR><TR><TD>CostCo_Washington</TD><TD>CostCo</TD><TD>Washington</TD><TD>12</TD><TD>1</TD><TD>100%</TD></TR><TR><TD>CostCo_Chicago</TD><TD>CostCo</TD><TD>Chicago</TD><TD>34</TD><TD>1</TD><TD>100%</TD></TR><TR><TD>CostCo_Marin</TD><TD>CostCo</TD><TD>Marin</TD><TD>1</TD><TD>1</TD><TD>100%</TD></TR><TR><TD>CostCo_Houston</TD><TD>CostCo</TD><TD>Houston</TD><TD>2</TD><TD>1</TD><TD>100%</TD></TR><TR><TD>CarreFour_New York</TD><TD>CarreFour</TD><TD>New York</TD><TD>56</TD><TD>1</TD><TD>400%</TD></TR><TR><TD>CarreFour_Washington</TD><TD>CarreFour</TD><TD>Washington</TD><TD>12</TD><TD>1</TD><TD>75%</TD></TR><TR><TD>CarreFour_Chicago</TD><TD>CarreFour</TD><TD>Chicago</TD><TD>0</TD><TD>0</TD><TD>0%</TD></TR><TR><TD>CarreFour_Marin</TD><TD>CarreFour</TD><TD>Marin</TD><TD>20</TD><TD>1</TD><TD>111%</TD></TR><TR><TD>CarreFour_Houston</TD><TD>CarreFour</TD><TD>Houston</TD><TD>18</TD><TD>1</TD><TD>86%</TD></TR><TR><TD>CarreFour_New York</TD><TD>CarreFour</TD><TD>New York</TD><TD>16</TD><TD>1</TD><TD>114%</TD></TR><TR><TD>CarreFour_Washington</TD><TD>CarreFour</TD><TD>Washington</TD><TD>17</TD><TD>1</TD><TD>106%</TD></TR><TR><TD>CarreFour_Chicago</TD><TD>CarreFour</TD><TD>Chicago</TD><TD>21</TD><TD>1</TD><TD>111%</TD></TR><TR><TD>CarreFour_Marin</TD><TD>CarreFour</TD><TD>Marin</TD><TD>12</TD><TD>1</TD><TD>67%</TD></TR><TR><TD>CarreFour_Houston</TD><TD>CarreFour</TD><TD>Houston</TD><TD>17</TD><TD>1</TD><TD>81%</TD></TR><TR><TD>CarreFour_New York</TD><TD>CarreFour</TD><TD>New York</TD><TD>14</TD><TD>1</TD><TD>100%</TD></TR><TR><TD>CarreFour_Washington</TD><TD>CarreFour</TD><TD>Washington</TD><TD>16</TD><TD>1</TD><TD>100%</TD></TR><TR><TD>CarreFour_Chicago</TD><TD>CarreFour</TD><TD>Chicago</TD><TD>19</TD><TD>1</TD><TD>100%</TD></TR><TR><TD>CarreFour_Marin</TD><TD>CarreFour</TD><TD>Marin</TD><TD>18</TD><TD>1</TD><TD>100%</TD></TR><TR><TD>CarreFour_Houston</TD><TD>CarreFour</TD><TD>Houston</TD><TD>21</TD><TD>1</TD><TD>100%</TD></TR><TR><TD>Walmart_New York</TD><TD>Walmart</TD><TD>New York</TD><TD>20</TD><TD>1</TD><TD>125%</TD></TR><TR><TD>Walmart_Washington</TD><TD>Walmart</TD><TD>Washington</TD><TD>18</TD><TD>1</TD><TD>138%</TD></TR><TR><TD>Walmart_Chicago</TD><TD>Walmart</TD><TD>Chicago</TD><TD>0</TD><TD>0</TD><TD>0%</TD></TR><TR><TD>Walmart_Marin</TD><TD>Walmart</TD><TD>Marin</TD><TD>22</TD><TD>1</TD><TD>169%</TD></TR><TR><TD>Walmart_Houston</TD><TD>Walmart</TD><TD>Houston</TD><TD>25</TD><TD>1</TD><TD>114%</TD></TR><TR><TD>Walmart_New York</TD><TD>Walmart</TD><TD>New York</TD><TD>12</TD><TD>1</TD><TD>75%</TD></TR><TR><TD>Walmart_Washington</TD><TD>Walmart</TD><TD>Washington</TD><TD>12</TD><TD>1</TD><TD>92%</TD></TR><TR><TD>Walmart_Chicago</TD><TD>Walmart</TD><TD>Chicago</TD><TD>0</TD><TD>0</TD><TD>0%</TD></TR><TR><TD>Walmart_Marin</TD><TD>Walmart</TD><TD>Marin</TD><TD>16</TD><TD>1</TD><TD>123%</TD></TR><TR><TD>Walmart_Houston</TD><TD>Walmart</TD><TD>Houston</TD><TD>20</TD><TD>1</TD><TD>91%</TD></TR><TR><TD>Walmart_New York</TD><TD>Walmart</TD><TD>New York</TD><TD>16</TD><TD>1</TD><TD>100%</TD></TR><TR><TD>Walmart_Washington</TD><TD>Walmart</TD><TD>Washington</TD><TD>13</TD><TD>1</TD><TD>100%</TD></TR><TR><TD>Walmart_Chicago</TD><TD>Walmart</TD><TD>Chicago</TD><TD>22</TD><TD>1</TD><TD>100%</TD></TR><TR><TD>Walmart_Marin</TD><TD>Walmart</TD><TD>Marin</TD><TD>13</TD><TD>1</TD><TD>100%</TD></TR><TR><TD>Walmart_Houston</TD><TD>Walmart</TD><TD>Houston</TD><TD>22</TD><TD>1</TD><TD>100%</TD></TR><TR><TD>CostCo_New York</TD><TD>CostCo</TD><TD>New York</TD><TD>24</TD><TD>1</TD><TD>100%</TD></TR><TR><TD>CostCo_Washington</TD><TD>CostCo</TD><TD>Washington</TD><TD>37</TD><TD>1</TD><TD>308%</TD></TR><TR><TD>CostCo_Chicago</TD><TD>CostCo</TD><TD>Chicago</TD><TD>34</TD><TD>1</TD><TD>100%</TD></TR><TR><TD>CostCo_Marin</TD><TD>CostCo</TD><TD>Marin</TD><TD>26</TD><TD>1</TD><TD>2600%</TD></TR><TR><TD>CostCo_Houston</TD><TD>CostCo</TD><TD>Houston</TD><TD>28</TD><TD>1</TD><TD>1400%</TD></TR><TR><TD>CarreFour_New York</TD><TD>CarreFour</TD><TD>New York</TD><TD>50</TD><TD>1</TD><TD>357%</TD></TR><TR><TD>CarreFour_Washington</TD><TD>CarreFour</TD><TD>Washington</TD><TD>44</TD><TD>1</TD><TD>275%</TD></TR><TR><TD>CarreFour_Chicago</TD><TD>CarreFour</TD><TD>Chicago</TD><TD>29</TD><TD>1</TD><TD>153%</TD></TR><TR><TD>CarreFour_Marin</TD><TD>CarreFour</TD><TD>Marin</TD><TD>49</TD><TD>1</TD><TD>272%</TD></TR><TR><TD>CarreFour_Houston</TD><TD>CarreFour</TD><TD>Houston</TD><TD>40</TD><TD>1</TD><TD>190%</TD></TR><TR><TD>Walmart_New York</TD><TD>Walmart</TD><TD>New York</TD><TD>27</TD><TD>1</TD><TD>169%</TD></TR><TR><TD>Walmart_Washington</TD><TD>Walmart</TD><TD>Washington</TD><TD>31</TD><TD>1</TD><TD>238%</TD></TR><TR><TD>Walmart_Chicago</TD><TD>Walmart</TD><TD>Chicago</TD><TD>45</TD><TD>1</TD><TD>205%</TD></TR><TR><TD>Walmart_Marin</TD><TD>Walmart</TD><TD>Marin</TD><TD>41</TD><TD>1</TD><TD>315%</TD></TR><TR><TD>Walmart_Houston</TD><TD>Walmart</TD><TD>Houston</TD><TD>27</TD><TD>1</TD><TD>123%</TD></TR></TBODY></TABLE><P> </P><P><BR />The thing is that I want to create averages (ignoring zero values) of Schezwan/Juice, i.e.</P><P>Desired Results (Schezwan/Juice):</P><TABLE><TBODY><TR><TD> </TD><TD> </TD><TD>CostCo</TD><TD>Carrefour</TD><TD>Walmart</TD><TD>Average</TD></TR><TR><TD>Juice</TD><TD>New York</TD><TD>x</TD><TD>y</TD><TD>z</TD><TD>Average(x,y,z)</TD></TR><TR><TD>Juice</TD><TD>Washington</TD><TD>a</TD><TD>b</TD><TD>c</TD><TD>Average(a,b,c)</TD></TR><TR><TD>Juice</TD><TD>Chicago</TD><TD>d</TD><TD>e</TD><TD>f</TD><TD>Average(d,e,f)</TD></TR><TR><TD>Juice</TD><TD>Marin</TD><TD>g</TD><TD>h</TD><TD>i</TD><TD>Average(g,h,i)</TD></TR><TR><TD>Juice</TD><TD>Houston</TD><TD>j</TD><TD>k</TD><TD>l</TD><TD>Average(j,k,l)</TD></TR></TBODY></TABLE><P> </P><P>Attached is the:<BR />Excel: <A href="https://drive.google.com/file/d/1andiSgGZowdBHlud9C1YQ7icfNNgqhkd/view?usp=sharing" target="_blank">https://drive.google.com/file/d/1andiSgGZowdBHlud9C1YQ7icfNNgqhkd/view?usp=sharing</A><BR />PBIX: <A href="https://drive.google.com/file/d/1Y4rIOUl_tWdewzP8FPwPaqF9eQnAPTOO/view?usp=sharing" target="_blank" rel="noopener">https://drive.google.com/file/d/1Y4rIOUl_tWdewzP8FPwPaqF9eQnAPTOO/view?usp=sharing</A></P><P> </P><P>In the PBIX, I wrote a measure, which can be filtered to get Schezwan/Juice in the above matrix (i.e. z,y,z,a,b,c,d,e,f,g,h,i,j,k,l)</P><P> </P><P>Measure = CALCULATE(SUM(Table1[Numbers]),FILTER(Table1,Table1[Fruits]="Schezwan"))/CALCULATE(SUM(Table1[Numbers]),FILTER(Table1,Table1[Fruits]="Juice"))</P><P> </P><P>But I would like to create a simple average (ignoring zeroes) and without creating multiple measures and doing an average of that. Any help in this regard would be extremely helpful.</P><P> </P><P>Regards,</P><P>Soubhik.</P>Tue, 08 Dec 2020 06:12:03 GMThttps://community.powerbi.com/t5/Desktop/How-to-create-an-average-of-a-category-of-a-items-from-a-table/m-p/1534548#M629908soubhik2020-12-08T06:12:03ZRe: How to create an average of a category of a items from a table with a list of data of different
https://community.powerbi.com/t5/Desktop/How-to-create-an-average-of-a-category-of-a-items-from-a-table/m-p/1534553#M629910
<P><LI-USER uid="270132"></LI-USER> I'm sorry, I don't fully understand what you want as end result. What do you mean by average of averages? It looks like you're comparing Schezwan to other fruits as a percent? To just get standard average, you can use the AVERAGE function, with Table[Shop] in Columns in the matrix. Please provide actual sample values, not just x, y, z and any details on what you mean by 'average' so we can assist further.</P>Sun, 06 Dec 2020 06:01:28 GMThttps://community.powerbi.com/t5/Desktop/How-to-create-an-average-of-a-category-of-a-items-from-a-table/m-p/1534553#M629910AllisonKennedy2020-12-06T06:01:28ZRe: How to create an average of a category of a items from a table with a list of data of different
https://community.powerbi.com/t5/Desktop/How-to-create-an-average-of-a-category-of-a-items-from-a-table/m-p/1534806#M629976
<P>Hi Allison,</P><P> </P><P>I have attached the excel and PBIX in the original post with the data.<BR /><BR />I represented what was percieved result in terms of 'x,y,z....' But the real data terms is available in the excel and PBIX attached.</P><P> </P><P>Attached again:<BR />Excel: <A href="https://drive.google.com/file/d/1andiSgGZowdBHlud9C1YQ7icfNNgqhkd/view?usp=sharing" target="_blank" rel="noopener nofollow noopener noreferrer">https://drive.google.com/file/d/1andiSgGZowdBHlud9C1YQ7icfNNgqhkd/view?usp=sharing</A><BR />PBIX: <A href="https://drive.google.com/file/d/1Y4rIOUl_tWdewzP8FPwPaqF9eQnAPTOO/view?usp=sharing" target="_blank" rel="noopener nofollow noopener noreferrer">https://drive.google.com/file/d/1Y4rIOUl_tWdewzP8FPwPaqF9eQnAPTOO/view?usp=sharing</A></P>Sun, 06 Dec 2020 15:33:03 GMThttps://community.powerbi.com/t5/Desktop/How-to-create-an-average-of-a-category-of-a-items-from-a-table/m-p/1534806#M629976soubhik2020-12-06T15:33:03ZRe: How to create an average of a category of a items from a table with a list of data of different
https://community.powerbi.com/t5/Desktop/How-to-create-an-average-of-a-category-of-a-items-from-a-table/m-p/1536061#M630301
<P><LI-USER uid="270132"></LI-USER> I have looked at the Excel file, but don't know which values you wish to use for x, y, z. What should the value of x be exactly? Which column are you wanting to average? Have you tried: </P>
<P> </P>
<P>Result = AVERAGEX(FILTER(Table, Table[Column] <> 0), Table[Column])</P>Mon, 07 Dec 2020 09:09:27 GMThttps://community.powerbi.com/t5/Desktop/How-to-create-an-average-of-a-category-of-a-items-from-a-table/m-p/1536061#M630301AllisonKennedy2020-12-07T09:09:27ZRe: How to create an average of a category of a items from a table with a list of data of different
https://community.powerbi.com/t5/Desktop/How-to-create-an-average-of-a-category-of-a-items-from-a-table/m-p/1537881#M630839
<P>Oh, I am sorry it was not clear over there, but x is no of juice at costco at new york location. Attached are following screens:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="soubhik_1-1607407214994.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/412093i49384FE8E7DB3E0A/image-size/medium?v=v2&px=400" role="button" title="soubhik_1-1607407214994.png" alt="soubhik_1-1607407214994.png" /></span> CostCo Schezwan by Juice in New York</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="soubhik_2-1607407315000.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/412097i53BA99CE2370CCE6/image-size/medium?v=v2&px=400" role="button" title="soubhik_2-1607407315000.png" alt="soubhik_2-1607407315000.png" /></span> Averageif(<>0)</P><P> </P><P>Updated in the excel as well: <A href="https://drive.google.com/file/d/1andiSgGZowdBHlud9C1YQ7icfNNgqhkd/view?usp=sharing" target="_blank">https://drive.google.com/file/d/1andiSgGZowdBHlud9C1YQ7icfNNgqhkd/view?usp=sharing</A><BR /><BR />So, like here I dont want to create a new calc table or calc column, but if there can be a measure through which I can obtain the average measure. I was looking for that.</P><P> </P><P> </P><P> </P>Tue, 08 Dec 2020 06:06:06 GMThttps://community.powerbi.com/t5/Desktop/How-to-create-an-average-of-a-category-of-a-items-from-a-table/m-p/1537881#M630839soubhik2020-12-08T06:06:06ZRe: How to create an average of a category of a items from a table with a list of data of different
https://community.powerbi.com/t5/Desktop/How-to-create-an-average-of-a-category-of-a-items-from-a-table/m-p/1544152#M632428
<P>Hi, <LI-USER uid="270132"></LI-USER> </P>
<P> </P>
<P>I'd like to suggest you try the following measure. The pbix file is attached in the end.</P>
<LI-CODE lang="markup">Re =
var tab =
SUMMARIZE(
Table1,
Table1[City],
Table1[Shop],
"Result",
DIVIDE(
CALCULATE(SUM(Table1[Numbers]),FILTER(Table1,Table1[Fruits]="Schezwan")),
CALCULATE(SUM(Table1[Numbers]),FILTER(Table1,Table1[Fruits]="Juice"))
)
)
return
AVERAGEX(
FILTER(
tab,
[Result]<>0
),
[Result]
)</LI-CODE>
<P> </P>
<P>Result:</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="d1.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/414331iF32398EAA611F167/image-size/medium?v=v2&px=400" role="button" title="d1.png" alt="d1.png" /></span></P>
<P> </P>
<P>Best Regards</P>
<P>Allan</P>
<P> </P>
<P><SPAN style="font-size: 10.0pt; font-family: 'Arial',sans-serif;">If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.</SPAN></P>
<P> </P>Fri, 11 Dec 2020 01:29:43 GMThttps://community.powerbi.com/t5/Desktop/How-to-create-an-average-of-a-category-of-a-items-from-a-table/m-p/1544152#M632428v-alq-msft2020-12-11T01:29:43ZRe: How to create an average of a category of a items from a table with a list of data of different
https://community.powerbi.com/t5/Desktop/How-to-create-an-average-of-a-category-of-a-items-from-a-table/m-p/1545438#M632742
<P>Thank you very much.</P>Fri, 11 Dec 2020 11:44:31 GMThttps://community.powerbi.com/t5/Desktop/How-to-create-an-average-of-a-category-of-a-items-from-a-table/m-p/1545438#M632742soubhik2020-12-11T11:44:31Z