Grand average of column average per category

[Anonymous]

Hi,

I have a dataset reflecting a process where suppliers ("company_ID") submits their offers ("% offered") in a tender round ("bidding_round").

- (1) Firms with 1-499 employees

- (2) Firms with 500+ employees

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:

- (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).

- (2) The average should be an average of each firm's average price (so ultimately e.g. 34,10% rather than 34,84% in the 500+ segment).

Company_ID	Bidding_round	% offered	Employees	Segment chosen?
2	339	16,0%	660	TRUE
3	339	25,1%	660	TRUE
4	339	42,5%	660	TRUE
2	340	49,8%	890	TRUE
2	354	38,0%	1200	TRUE
4	354	35,6%	1200	TRUE
3	354	27,3%	1200	TRUE
1	354	52,0%	1200	TRUE
2	341	44,4%	670	TRUE
19	339	40,3%	660	TRUE
20	354	58,3%	1200	TRUE
20	770	80%	200
20	989	85%	350
1	765	82,%	100

Any help is greatly appreciated. Thank you.

[Greg_Deckler · Accepted solution]

OK, I did what I think is correct but not sure. See PBIX.

Average of the Average = 
    AVERAGEX(
        FILTER(
            SUMMARIZE(
                'Table',
                [Company_ID],
                "__Count",COUNTROWS('Table'),
                "__Average",AVERAGE('Table'[% offered])
            ),
            [__Count] > 1
        ),
        [__Average]
    )

[Anonymous]

Excellent, worked a charm. Thank you for the fast reply!

[Greg_Deckler]

Sweet! Always helps to provide the data so thank-you very much for that! You made it easy!