March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
11-18-2024 22:22 PM - last edited 11-19-2024 18:38 PM
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
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..
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.
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: