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
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!