Need help with grouping values based on a measure

[lasmithfla]

This is a sample of the data.

The number of Sales is a measure so not a column in the table.

I'm struggling with a calc that will count the number of customers bases on the sales ranges.  I can't do anything that is counting rows in the original sales table because the ones with 0 value do not exist in that table.

So I'm thinking the calc needs to create a temp table like i have pasted below in the green to base the calc. on.

Hope this makes sense.

[ryan_mayu · Accepted solution]

@lasmithfla 

you can try this

Column = 
VAR _min='Sales Range'[min]
VAR _max='Sales Range'[max]
VAR tbl=SUMMARIZE(Customer,Customer[Customer],"no of sales",COUNTROWS(RELATEDTABLE(Sales))+0)
return COUNTX(FILTER(tbl,[no of sales]>=_min&&[no of sales]<=_max),[no of sales])+0

pls see the attachment below

[coskuersanli]

Hi @lasmithfla 

I believe we can use SUMMARISE() and COUNTROWS(). If 0's don't show up in table, we can add ISBLANK() to calculation. Can you try something like this one please? 🙂

VAR summ_table =
		SUMMARISE
			(
				Table,
				Customer,
				"new_column",
				number_of_sales
			)

RETURN

CALCULATE
	(
		COUNTROWS(summ_table),
		FILTER
			(
				summ_table,
				new_column >= sales_ranges_min &&
				new_column < sales_ranges_max
			)
	)