amitchandak's avatar
amitchandak
Super User
5 years ago

Dynamic Segmentation/ Bucketing/ Binning

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 %])

 

 

 

Mathematical
Totals

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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:

     

     
  • alialsayer's avatar
    alialsayer
    Helper I
    3 years ago

    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.

  • gp62061's avatar
    gp62061
    Regular Visitor
    1 year ago

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