Tagging each unique ID by using the yearly average

[Kansetsuwaza]

Dear Community,

I have a challenge with some external data that I’d hope the BI query would help me with.

I would like to tag the property IDs in an occupancy rate range (i.e. 0%-10%, 10%-20% etc... ).
This tag should be calculated by using the average occupancy rate of each year for each unique ID.

For this reason, this tag may be under different ranges for each year.

You may find a sample .pbix HERE

Property ID	Year	Occ Range
ABC1	2018	10%-20%
ABC1	2019	20%-30%
ABC1	2020	70%-80%

Thus, I will be able to see the number of IDs that are taking place under different ranges for each year.

[Anonymous · Accepted solution]

Hi  @Kansetsuwaza ,

Here are the steps you can follow：

1. Create calculated column.

Flag =
SWITCH(
    TRUE(),
    'Test'[Occupancy]>=0&&'Test'[Occupancy]<=0.1,"0%-10%",
    'Test'[Occupancy]>0.1&&'Test'[Occupancy]<=0.2,"10%-20%",
    'Test'[Occupancy]>0.2&&'Test'[Occupancy]<=0.3,"20%-30%",
    'Test'[Occupancy]>0.3&&'Test'[Occupancy]<=0.4,"30%-40%",
    'Test'[Occupancy]>0.4&&'Test'[Occupancy]<=0.5,"40%-50%",
    'Test'[Occupancy]>0.5&&'Test'[Occupancy]<=0.6,"50%-60%",
    'Test'[Occupancy]>0.6&&'Test'[Occupancy]<=0.7,"60%-70%",
    'Test'[Occupancy]>0.7&&'Test'[Occupancy]<=0.8,"70%-80%",
    'Test'[Occupancy]>0.8&&'Test'[Occupancy]<=0.9,"80%-90%",
    'Test'[Occupancy]>0.9&&'Test'[Occupancy]<=1,"90%-100%")

year =
YEAR('Test'[Reporting Month])

2. Create measure.

Measure =
DISTINCTCOUNT('Test'[Property ID])

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

[amitchandak]

@Kansetsuwaza , You need consider

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

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