Last 12 Months of Sales into Bins

[SonyT01]

Hi,

I would like to be able to allocate our last 12 months of sales into the below bins.  I have managed to do this by customer count but due to my lack of knowledge of DAX, I can't seem to work out how to do this with the sales amount. 

The customer count measure that works perfectly is this:

Count of Customers by Sales Bin = SUMX (Bins, COUNTROWS(FILTER(SUMMARIZE(Customers, Customers[Customer Name Grp1]), SalesSplitPrep[Total Sales Last 12 Months] >= Bins[Min Value] && SalesSplitPrep[Total Sales Last 12 Months] < Bins[Max Value])))

But how do I do this with Sales ie allocate the 'sum of the last 12 month sales by customer' into the below bins?  It's important that the last 12 mths of sales are grouped by Customer Name Grp1 and Sale_type before determining the sale size/bin.

The 'Bins' table looks like this.

The 'SalesSplitPrep' table looks like this:

My end goal is to create a matrix that is similar to this, showing sales $$.

Hope someone can help. 🙂

[V-lianl-msft]

Hi @SonyT01 ,

According to your description, if you need to group by customer name and display it in the matrix, the customer name field is essential in the matrix. Please share a sample pbix with virtual data.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[SonyT01]

Hi Ashish,

Thank you for your help but as it is senstive information I would rather not share the file.

Thanks.

[amitchandak]

@SonyT01 , refer my blog and Video for the same

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[SonyT01]

Hi Amit,

Thank you for your links.  I tried applying one of your measures by switching the AVERAGEX with a SUMX but it's not recognising my 'Bins' table.  Can you see where I might have gone wrong in my measure below?  The column 'Customers'[Customer Name Grp1] is a text field so does that have something to do with it? 

Thank you.