Forum Discussion
Tagging each unique ID by using the yearly average
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.
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
2 Replies
- amitchandak
Super User
Kansetsuwaza , You need consider
Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k 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
