Fixed bins on dynamically selected data

[milpro011]

Hi, I have a sales table with data of the lowest granularity (salesperson, client, product, date, amount, quantity, etc.)

With several on-page filters, I have a simple output table: 

Column 1 - Salesperson
Column 2 - # of products sold

I'm looking to create several bins based on products sold to plot a histogram, for instance
1) 1 - 6 products sold

2) 7 - 13 products sold, etc.

so that I can say, 20 sales reps sold 1-6 products, 30 sales reps sold 7-13, etc. 

Is this possible to build such a graph without derived tables, simply using measures for instance? 

Thank you!

[vanessafvg]

@milpro011  are you able to provide some data? ie. copy and paste same into the window?

[milpro011]

@vanessafvg thanks!

A simplified dataset is along the following lines (sorry, I can't paste the actual data for privacy reasons). 

Year  |    Salesperson    |   Product |  Lot# |   Qty 

2019         Joe                   Cups        A1         3

2019         Joe                   Cups        A2         4

2019         Matt                 Cups        A1         4

2019         Matt                 Cups        A5        4

With filters :

Year: 2019

Product: Cups

And using a regular PowerBi table, data is reduced to:

Salesperson | Qty

Joe                7

Matt              8

James            3

Greg              2

Mitch             9

etc.

I'm looking to bucket and count how many sales reps have sold a certain amount of product

If we have 2 buckets: 1-6 and 7-13, we would have the answer 

Bucket | # of Salespeople

1-6               2

7-13             3 

Thanks!

[v-chuncz-msft · Accepted solution]

@milpro011 ,

You may refer to the following post.

https://community.powerbi.com/t5/Desktop/Create-a-bar-graph-with-measures-as-axis/td-p/455900

[milpro011]

@v-chuncz-msft Thanks! I was able to create a Measure that works i.e. returns my buckets

SalesBucketM = IF( Calculate(count(Sales[Products]),values(Sales[Salesperson]))<7, "<7", IF( Calculate(count(Sales[Products]),values(Sales[Salesperson]))>=7 && Calculate(count(Sales[Products]), values(Sales[Salesperson]))<14, "7-13", "14+"))

When I add that measure to a PowerBI table that has the Salesperson as one of the columns, it correctly computes and works. However, I can't add measures to a Matrix or set it as an Axis on a graph. Furthermore, It won't work unless I have the Salesperson as one of the columns, hence I can't do "Count(Distinct)" on the Salesperson to figure out the frequency i.e. the number of salespeople that fall under each bucket.

I tried creating a column with the exact same code, however, it always returns <7 it probably just sees that 1 line has always 1 product, and doesn't sum up the entire table. 

It feels like I'm almost there, but I'm afraid that I might be hitting a dead-end too. Any help is greatly appreciated. 

Thanks!