topic Re: Groups/Bins from a Measure in DAX Commands and Tips

Groups/Bins from a Measure

Tob_P — Thu, 23 Mar 2023 15:11:23 GMT

Hi,

Done a fair bit of reading on this but can't find a use case scenario similar so wanted to ask here. Inherited a report which has the following Measure...

Sum of Max Amount = SUMX(DISTINCT('NAV_Sales Header'[OTP No_]),[Max Amount])+0

...which essentially gives me the Maximum Value. What I need to be able to do is create a calculated column that groups the max amount into the following groupings. I've created this unrelated table (tbl_BinTable) in my .pbix

Range NameMinMaxIndex

£0 - £5,000	0	5000	1
£5,000 - £20,000	5000	20000	2
£20,000 - £50,000	20000	50000	3
£50,000 - £100,000	50000	100000	4
£100,000+	100000	5000000	5

Is it possible to create groupings based on a Measure?

Apologies, on this one I don't have any sample data to share.

Re: Groups/Bins from a Measure

amitchandak — Fri, 24 Mar 2023 05:25:42 GMT

Tob_P , refer

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

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

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q

Re: Groups/Bins from a Measure

Tob_P — Fri, 24 Mar 2023 09:15:22 GMT

Hi amitchandak

Thanks for sharing - I'm looking more specifically for a calculated column, rather than a measure, so that I can use those bandings in a Slicer to filter by.

Re: Groups/Bins from a Measure

Anonymous — Mon, 27 Mar 2023 06:45:22 GMT

Hi Tob_P ,

Here are the steps you can follow：

1. Create calculated column.

Column = MAXX( FILTER(ALL('Table'), 'NAV_Sales Header'[Sum of Max Amount]>='Table'[Min] && 'NAV_Sales Header'[Sum of Max Amount]<='Table'[Max]),[Index])

2. Result:

Best Regards,

Liu Yang

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