# Return count of unique records in one field, based on sum of another field

#### btreg

##### New Member
To illustrate the question I have created some dummy data below:

 Country Product Sales Volume France Shoes 10 France Shoes 20 France Shoes 30 France Boots 5 France Boots 5 France Hat 10 France Hat 5

<tbody>
</tbody>

I want to create a measure that will return the number of products with a total sales volume of less than 20 (i.e. the result would be "2" from the above data, as "Boots" and "Hats" have sales volumes of 10 and 15 respectively.

Any help with creating the measure would be much appreciated. Thanks in advance!

Ben

##### MrExcel MVP
 Country Product Sales Volume Subtotal 20 France Shoes 10 60 2 France Shoes 20 60 Boots France Shoes 30 60 Hat France Boots 5 10 France Boots 5 10 France Hat 10 15 France Hat 5 15

<tbody>
</tbody>

In D2 just enter and copy down:

=SUMIFS(\$C\$2:\$C\$8,\$B\$2:\$B\$8,\$B2)

In E2 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(\$B\$2:\$B\$8=""),IF(\$D\$2:\$D\$8>0,IF(\$D\$2:\$D\$8<E1,MATCH(\$B\$2:\$B\$8,\$B\$2:\$B\$8,0)))),ROW(\$B\$2:\$B\$8)-ROW(\$B\$2)+1),1))

In E3 control+shift+enter, not just enter, and copy down:

=IF(ROWS(\$E\$3:E3)>\$E\$2,"",INDEX(\$B\$2:\$B\$8,SMALL(IF(FREQUENCY(IF(1-(\$B\$2:\$B\$8=""),IF(\$D\$2:\$D\$8>0,IF(\$D\$2:\$D\$8<\$E\$1,MATCH(\$B\$2:\$B\$8,\$B\$2:\$B\$8,0)))),ROW(\$B\$2:\$B\$8)-ROW(\$B\$2)+1),ROW(\$B\$2:\$B\$8)-ROW(\$B\$2)+1),ROWS(\$E\$3:E3))))

#### btreg

##### New Member

Thank you for the reply, but I need to create this in the form of a DAX measure, so it will work dynamically within a Pivot Table.

Ben

##### Well-known Member
=sumx(values(Table1[Product]),if([Sum of Sales Volume]<20,1,0))

or you could have a calculated field with:

=if(CALCULATE(SUMX(Table1,[Sales Volume]),FILTER(Table1,[Product]=EARLIER([Product])))<20,1,0)/CALCULATE(countX(Table1,[Sales Volume]),FILTER(Table1,[Product]=EARLIER([Product])))

#### btreg

##### MrExcel MVP

Thank you for the reply, but I need to create this in the form of a DAX measure, so it will work dynamically within a Pivot Table.

Ben
I missed that...

By the way, just for the record, the formula in E2 is:

{=SUM(IF(FREQUENCY(IF(1-(\$B\$2:\$B\$8=""),IF(\$D\$2:\$D\$8>0,IF(\$D\$2:\$D\$8 < E1,MATCH(\$B\$2:\$B\$8,\$B\$2:\$B\$8,0)))),ROW(\$B\$2:\$B\$8)-ROW(\$B\$2)+1),1))}

1,082,117
Messages
5,363,256
Members
400,723
Latest member
Jsdk

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...