Forum Discussion

milpro011's avatar
milpro011
Helper I
7 years ago
Solved

Fixed bins on dynamically selected data

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 - ...
Need Help
vanessafvg's avatar
vanessafvg
Community Champion
7 years ago

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

milpro011's avatar
milpro011
Helper I
7 years ago

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!

    • milpro011's avatar
      milpro011
      Helper I
      7 years ago

      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!