How can I make a device to be counted in one bucket instead of multiple buckets?

[Farhat]

I want to create a visual in which there are buckets based on the values of #ofStarts column in the X axis and count of Device  on the Y axis but one device should be counted in only one bucket. This bucket should be the one with the maximum value in the #ofStarts column.

Here is the dummy data to explain the issue.

Device	Date	#ofStarts
A	20-Feb-24	29
A	19-Feb-24	28
A	18-Feb-24	13
A	17-Feb-24	10
B	19-Feb-24	19
B	18-Feb-24	6
C	20-Feb-24	25
D	17-Feb-24	14
D	16-Feb-24	9
E	19-Feb-24	2

On considering a bucket size of 10, we get three buckets for #ofStarts column; 1-10, 11-20 and 21-30. 5 devices have to be sorted in the three buckets. A device has to be put into that bucket against which the #ofStarts has the maximum value for the date range selected in the Date slicer.

For the above data and date range with all the dates, this is how Power BI displays this data.

But Expectation is as shown below. Device A has the maximum #ofStarts as 29 and it should be counted in 21-30 bucket and not any other bucket and device B and D have to be counted in 11-20 and E in 1-10.

But this has to change when the date slicer is changed. When selecting the date slicer to show data anyday until 18 Feb the following visual is expected.

How can I acieve this? Please help. Thank you! 

[Anonymous · Accepted solution]

Hi  @Farhat ,

Here are the steps you can follow：

1. enter data -- create table.

2. Create measure.

Max_Group =
MAXX(
    FILTER(ALLSELECTED('Table'),
    'Table'[Device]=MAX('Table'[Device])),[#ofStarts])

Measure =
SWITCH(
    TRUE(),
    MAX('Slicer_Table'[Slicer])="1-10",
    CALCULATE(DISTINCTCOUNT('Table'[Device]),FILTER(ALLSELECTED('Table'),[Max_Group]>=1&&[Max_Group]<=10)),
    MAX('Slicer_Table'[Slicer])="11-20",
    CALCULATE(DISTINCTCOUNT('Table'[Device]),FILTER(ALLSELECTED('Table'),[Max_Group]>=11&&[Max_Group]<=20)),
    MAX('Slicer_Table'[Slicer])="21-30",
    CALCULATE(DISTINCTCOUNT('Table'[Device]),FILTER(ALLSELECTED('Table'),[Max_Group]>=21&&[Max_Group]<=30)))

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

[Farhat]

@Anonymous I am very grateful to you. You took out the time to look through a lenghty question and provided such a detailed solution. Thanks a lot! May your issues be solved soon too 🙂 

[amitchandak]

@Farhat , You have bucket the Max value #ofStarts or the the latest value

Latest Date
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

Once you have the measure, you need to segment the measure

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ

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

[Farhat]

@amitchandak Thank you so much for investing your time into putting together the solution piece by piece and sharing all those references with me. This means a lot. And wow you have a youtube channel (https://www.youtube.com/@AmitChandak) as well. You have earned a new subscrier today 🙂