Groups/Bins from a Measure

[Tob_P]

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.

[v-yangliu-msft · Accepted solution]

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

[amitchandak]

@Tob_P , refer

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

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

[Tob_P]

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.