topic Dynamic Segmentation/ Bucketing/ Binning in Quick Measures Gallery

Dynamic Segmentation/ Bucketing/ Binning

amitchandak — Tue, 22 Sep 2020 11:44:39 GMT

Dynamic Segmentation/ Bucketing/ Binning

Created an independent bucket Table.

Create measures that take advantage of those buckets

Actual Measure

Margin % = DIVIDE([Margin],[Sales])

The measure we would like to become the parameter/slicer

Margin Type = Switch( True(), [Margin %] < -.2 , "Very Bad", [Margin %] <0 , "Bad", [Margin %] <.1 , "Netural", [Margin %] <.25 , "Good", "Very Good" )

The independent Table we created

Start Limit	End Limit	Bucket
-1000	-0.2	Very Bad
-0.2	0	Bad
0	0.1	Netrual
0.1	0.25	Good
0.25	1000	Very Good

Bucketed measures

Margin Bucket = COUNTX(filter(VALUES(customer[Customer Id]),[Margin %] >=Min('Margin Bucket'[Start Limit]) && [Margin %] <max('Margin Bucket'[End Limit])),customer[Customer Id]) Avg Margin Bucket = AVERAGEX(filter(VALUES(customer[Customer Id]),[Margin %] >=Min('Margin Bucket'[Start Limit]) && [Margin %] <max('Margin Bucket'[End Limit])),[Margin %])

eyJrIjoiYmFmMTc0NzYtYzMzNS00NTU0LWFjNGYtODc4ZjA0ODM0MzVjIiwidCI6ImVhOGJkMWZkLWFjMzQtNGFlMi1iNDIxLTZjZmEyZmNmZjI0MyJ9

Re: Dynamic Segmentation/ Bucketing/ Binning

Anonymous — Wed, 06 Oct 2021 04:31:02 GMT

Hi Amit,

I have followed this and getting below result which is incorrect:

Total is correct but it is not segregating according to buckets.

Till here its fine,

But as soon as I add Measure as below it only gives total but does not segregate

My Measure is below:

Age Bucket = COUNTX(filter(VALUES(TrusteeClaimsList_Decision[PolicyCaseBenefitKey]),[Age of Pending Claims] >=Min('Age_Pending_Bucket Table'[Start Limit]) && [Age of Pending Claims] <=max('Age_Pending_Bucket Table'[End Limit])),TrusteeClaimsList_Decision[PolicyCaseBenefitKey])

Can you please help, it should be as below:

Re: Dynamic Segmentation/ Bucketing/ Binning

alialsayer — Tue, 22 Nov 2022 08:53:04 GMT

This is fantastic; thank you for your prompt response; however, I have a slightly different situation. First, I have to group values by date for each customer, and then I should use the bucket. I am trying to find a way to do this in the DAX function instead of grouping values for each customer & date in a table to avoid performance issues.

Re: Dynamic Segmentation/ Bucketing/ Binning

Anonymous — Wed, 20 Nov 2024 02:38:15 GMT

Hi Amit,
I am facing a different issue where the bins should be calculated based on the dynamic Min & Max (dynamic range of data based on category selection).
My data is like this:
Category A sales ranges from -300 to 400 ( need to create 20 bins in this range when I select 'A' in the category slicer;
Category B sales ranges from 200 to 800 ( need to create 20 bins in this range when I select 'B' in the category slicer; and so on..
Currently bins are creating based on the total range -300 to 800 which is not required.
Is there any workaround to achieve this?

Appreciate your inputs..Thanks in advance..