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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Product[/TD]
[TD]Sales Volume[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]Shoes[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]France
[/TD]
[TD]Shoes[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]Shoes[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]Boots[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]Boots[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]Hat[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]Hat[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

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]

[TABLE="class: grid, width: 348"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Product[/TD]
[TD]Sales Volume[/TD]
[TD]Subtotal[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]Shoes[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]Shoes[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]60[/TD]
[TD]Boots[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]Shoes[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]60[/TD]
[TD]Hat[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]Boots[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]Boots[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]Hat[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]Hat[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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