Average prices if a supplier appears twice in range within a specific segment

Hi there,

I have a worksheet where the user makes a choice between 3 segments (0-199 employees, 200-499 employees, and +500 employees) in a drop down, and then I need PowerPivot to average the prices for all firms with that number of employees (e.g. between 200-499 employees).

However, the complicating twist is that within the three segments, PowerPivot should only average the prices of suppliers that appear AT LEAST twice. So for instance in the table below, supplier 1 should be included when averaging prices in the 0 - 199 segment (since it appears twice), but not in the 500+ segment (since it appears only once).

Sample table.png

My idea is to make a calculated column or measure that evaluates to "TRUE" if the price should be included in the calculations and then add that TRUE as a report filter in the pivot table.

However, I am unsure how to build the formula in DAX.

Any help is greatly appreciated, thanks.

I did it with ordinary table entries and ordinary PivotTable ... just for quickness. It that suitable?

Thanks for the quick response Glenn - I found another solution directly in PowerPivot