Forum Discussion
Helper IIICan i count values within a measure?
Hi I am new to the forum so apologise if this is a little garbled.
Basically I need to know the count of values in different ranges within a measure….i think.
I have a data table as below and have added a calculated column to classify if the sensor was used during each specific day (>6 readings).
| Date | Code | 08:00:00 | 08:10:00 | 08:20:00 | 08:30:00 | 08:40:00 | 08:50:00 | 09:00:00 | 09:10:00 | 09:20:00 | 09:30:00 | 09:40:00 | 09:50:00 | 10:00:00 | Occupied for Day |
| 01/04/19 | Sensor1 | 1 | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 1 | 0 |
| 01/04/19 | Sensor2 | 1 | 0 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
| 01/04/19 | Sensor3 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 1 | 1 | 1 | 0 |
| 01/04/19 | Sensor4 | 1 | 1 | 1 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 1 | 1 | 1 |
| 01/04/19 | Sensor5 | 0 | 1 | 0 | 0 | 1 | 0 | 1 | 0 | 1 | 0 | 1 | 1 | 1 | 1 |
| 01/04/19 | Sensor6 | 1 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 1 | 1 | 0 |
| 01/04/19 | Sensor7 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 0 | 0 |
| 01/04/19 | Sensor8 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 |
| 01/04/19 | Sensor9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 1 | 1 | 0 |
| 02/04/19 | Sensor1 | 1 | 0 | 1 | 1 | 1 | 0 | 1 | 0 | 0 | 1 | 1 | 1 | 0 | 1 |
| 02/04/19 | Sensor2 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 1 | 1 | 0 |
| 02/04/19 | Sensor3 | 1 | 1 | 1 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 02/04/19 | Sensor4 | 0 | 1 | 0 | 1 | 1 | 0 | 0 | 0 | 1 | 1 | 1 | 0 | 0 | 0 |
| 02/04/19 | Sensor5 | 0 | 0 | 0 | 1 | 1 | 1 | 0 | 1 | 1 | 0 | 0 | 1 | 0 | 0 |
| 02/04/19 | Sensor6 | 1 | 1 | 1 | 0 | 1 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 0 |
| 02/04/19 | Sensor7 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 |
| 02/04/19 | Sensor8 | 0 | 0 | 0 | 1 | 1 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 1 | 0 |
| 02/04/19 | Sensor9 | 0 | 0 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 |
| 03/04/19 | Sensor1 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 1 | 1 | 0 | 1 | 0 | 1 | 0 |
| 03/04/19 | Sensor2 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 0 |
| 03/04/19 | Sensor3 | 1 | 1 | 1 | 1 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 1 |
| 03/04/19 | Sensor4 | 1 | 0 | 0 | 0 | 1 | 1 | 1 | 0 | 0 | 1 | 1 | 0 | 0 | 0 |
| 03/04/19 | Sensor5 | 0 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 1 | 1 | 0 | 0 | 1 | 0 |
| 03/04/19 | Sensor6 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 1 | 0 | 0 | 0 |
| 03/04/19 | Sensor7 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 |
| 03/04/19 | Sensor8 | 1 | 0 | 0 | 1 | 1 | 1 | 0 | 1 | 0 | 1 | 1 | 1 | 1 | 1 |
| 03/04/19 | Sensor9 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 1 | 0 |
In this example Sensor 1 is occupied for one day, with an occupied day’s percentage of 33.3% (1 out of 3 days).
I can produce a measure to count occupied days per sensor and a measure to calculate a sensors occupied percentage over a date range and populate a table visualisation.
| Code | Occupied for Day | Occupied % |
| Sensor1 | 1 | 33.3% |
| Sensor2 | 0 | 0.0% |
| Sensor3 | 1 | 33.3% |
| Sensor4 | 1 | 33.3% |
| Sensor5 | 1 | 33.3% |
| Sensor6 | 0 | 0.0% |
| Sensor7 | 1 | 33.3% |
| Sensor8 | 2 | 66.7% |
| Sensor9 | 1 | 33.3% |
I need a measure to count the number of sensors that were occupied in the following ranges 0%-20%, 20%-40%, 40%-60%, 60%-80%, 80%-100%.
All data is subject to a date range slicer on the report page.
I have only shown a short data set there are over 10,000 sensors with over a years’ worth of data
Please help.
Hi PaulHallam ,
Please create a table with decimal value of range start and end, then you can use following measure to summarize and look correspond records and get count of them.
Measure formula:
Summary Count = VAR temp = SUMMARIZE ( 'Sample', [Sensor Number], "Count", [Occupied Days], "Percent", [Occupied %] ) RETURN COUNTROWS ( FILTER ( temp, [Percent] >= MAX ( T4[Start] ) && [Percent] <= MAX ( T4[End] ) ) ) + 0Regards,
Xiaoxin Sheng
8 Replies
Hi PaulHallam ,
I'd like to suggest you do unpivot columns on your time fields to convert them to time attribute and value.
Then you can write condition based on time value instead hard code all time fields and compare with different time fields.
BTW, you can't direct aggregate with measure result. Since measure result are dynamic calculated based on its row contents. So you need to create variable summarized table to restore scenario, then you can do aggregate with this summary table.
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
PaulHallamThanks for the advice Xiaoxin. I had already tried this and got as far as the following table 'SUMMARY' but then got stuck;
Date Sensor Occupied for Day 01/01/2019 S1 1 01/01/2019 S2 1 01/01/2019 S3 1 01/01/2019 S4 1 01/01/2019 S5 1 02/01/2019 S1 1 02/01/2019 S2 1 02/01/2019 S3 1 02/01/2019 S4 0 02/01/2019 S5 1 03/01/2019 S1 0 03/01/2019 S2 0 …. …. …. In the above table sensor 1 was used on 2 out of 3 days, and i can easily calculate individual percentage use in a measure.
% Use = 'SUMMARY' [Occupied for day]/DISTINCTCOUNT('SUMMARY'[Date])
I need a measure to tell me the number of sensors that are in the ranges 0%-20%, 20%-40%, 40%-60%, 60%-80% and 80%-100%.
I am assuming i have to add a table with the ranges in but cant seem to work it out
Any thoughts, i am pretty new to Power BI so please explain logic for a solution in detail if possible,
Thanks PaulHi PaulHallam ,
Can you please share some sample data for test? Please do mask on sensitive data before share.
Regards,
Xiaoxin Sheng