topic Re: Grand average of column average per category in Desktop
https://community.powerbi.com/t5/Desktop/Grand-average-of-column-average-per-category/m-p/963684#M461818
<P>OK, I did what I think is correct but not sure. See PBIX.</P>
<P> </P>
<P> </P>
<LI-CODE lang="markup">Average of the Average =
AVERAGEX(
FILTER(
SUMMARIZE(
'Table',
[Company_ID],
"__Count",COUNTROWS('Table'),
"__Average",AVERAGE('Table'[% offered])
),
[__Count] > 1
),
[__Average]
)</LI-CODE>
<P> </P>Sun, 08 Mar 2020 21:27:30 GMTGreg_Deckler2020-03-08T21:27:30ZGrand average of column average per category
https://community.powerbi.com/t5/Desktop/Grand-average-of-column-average-per-category/m-p/963679#M461814
<P>Hi,</P><P> </P><P>I have a dataset reflecting a process where suppliers ("company_ID") submits their offers ("% offered") in a tender round ("bidding_round").</P><P>- (1) Firms with 1-499 employees</P><P>- (2) Firms with 500+ employees</P><P> </P><P>My end goal is to calculate the average price offered for either of the following two segments based on the user's input in another sheet ("Segment chosen?"), subject to two important criteria:</P><P>- (1) Only suppliers that have submitted at least 2 prices IN A SEGMET should be included (so supplier 19 and 20 should be excluded in 500+ segment, but supplier 20 should be included in the 1-499 segment where supplier 1 should then be exluded).</P><P>- (2) The average should be an average of <U>each firm's average price</U> (so ultimately e.g. 34,10% rather than 34,84% in the 500+ segment).</P><P> </P><TABLE><TBODY><TR><TD>Company_ID</TD><TD>Bidding_round</TD><TD>% offered</TD><TD>Employees</TD><TD>Segment chosen?</TD></TR><TR><TD>2</TD><TD>339</TD><TD>16,0%</TD><TD>660</TD><TD>TRUE</TD></TR><TR><TD>3</TD><TD>339</TD><TD>25,1%</TD><TD>660</TD><TD>TRUE</TD></TR><TR><TD>4</TD><TD>339</TD><TD>42,5%</TD><TD>660</TD><TD>TRUE</TD></TR><TR><TD>2</TD><TD>340</TD><TD>49,8%</TD><TD>890</TD><TD>TRUE</TD></TR><TR><TD>2</TD><TD>354</TD><TD>38,0%</TD><TD>1200</TD><TD>TRUE</TD></TR><TR><TD>4</TD><TD>354</TD><TD>35,6%</TD><TD>1200</TD><TD>TRUE</TD></TR><TR><TD>3</TD><TD>354</TD><TD>27,3%</TD><TD>1200</TD><TD>TRUE</TD></TR><TR><TD>1</TD><TD>354</TD><TD>52,0%</TD><TD>1200</TD><TD>TRUE</TD></TR><TR><TD>2</TD><TD>341</TD><TD>44,4%</TD><TD>670</TD><TD>TRUE</TD></TR><TR><TD>19</TD><TD>339</TD><TD>40,3%</TD><TD>660</TD><TD>TRUE</TD></TR><TR><TD>20</TD><TD>354</TD><TD>58,3%</TD><TD>1200</TD><TD>TRUE</TD></TR><TR><TD>20</TD><TD>770</TD><TD>80%</TD><TD>200</TD><TD> </TD></TR><TR><TD>20</TD><TD>989</TD><TD>85%</TD><TD>350</TD><TD> </TD></TR><TR><TD>1</TD><TD>765</TD><TD>82,%</TD><TD>100</TD><TD> </TD></TR></TBODY></TABLE><P> </P><P>Any help is greatly appreciated. Thank you.</P>Sun, 08 Mar 2020 21:10:19 GMThttps://community.powerbi.com/t5/Desktop/Grand-average-of-column-average-per-category/m-p/963679#M461814Phil1234567892020-03-08T21:10:19ZRe: Grand average of column average per category
https://community.powerbi.com/t5/Desktop/Grand-average-of-column-average-per-category/m-p/963684#M461818
<P>OK, I did what I think is correct but not sure. See PBIX.</P>
<P> </P>
<P> </P>
<LI-CODE lang="markup">Average of the Average =
AVERAGEX(
FILTER(
SUMMARIZE(
'Table',
[Company_ID],
"__Count",COUNTROWS('Table'),
"__Average",AVERAGE('Table'[% offered])
),
[__Count] > 1
),
[__Average]
)</LI-CODE>
<P> </P>Sun, 08 Mar 2020 21:27:30 GMThttps://community.powerbi.com/t5/Desktop/Grand-average-of-column-average-per-category/m-p/963684#M461818Greg_Deckler2020-03-08T21:27:30ZRe: Grand average of column average per category
https://community.powerbi.com/t5/Desktop/Grand-average-of-column-average-per-category/m-p/963689#M461822
<P>Excellent, worked a charm. Thank you for the fast reply!</P>Sun, 08 Mar 2020 21:56:31 GMThttps://community.powerbi.com/t5/Desktop/Grand-average-of-column-average-per-category/m-p/963689#M461822Phil1234567892020-03-08T21:56:31ZRe: Grand average of column average per category
https://community.powerbi.com/t5/Desktop/Grand-average-of-column-average-per-category/m-p/963702#M461829
<P>Sweet! Always helps to provide the data so thank-you very much for that! You made it easy!</P>Sun, 08 Mar 2020 23:06:26 GMThttps://community.powerbi.com/t5/Desktop/Grand-average-of-column-average-per-category/m-p/963702#M461829Greg_Deckler2020-03-08T23:06:26Z