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

[btreg]

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

 

[Aladin Akyurek]

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]

Hi Aladin,

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

 

[sheetspread]

=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]

Hi sheetspread - your first solution worked perfectly, thank you!

 

[Aladin Akyurek]

Hi Aladin,

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))}