Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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]
)
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]
)
Excellent, worked a charm. Thank you for the fast reply!
Sweet! Always helps to provide the data so thank-you very much for that! You made it easy!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |