09-22-2020 04:44 AM
Dynamic Segmentation/ Bucketing/ Binning
Created an independent bucket Table.
Create measures that take advantage of those buckets
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|
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 %])
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.
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: