Forum Discussion
Helper IIICan i count values within a measure?
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
Hi PaulHallam ,
Can you please share some sample data for test? Please do mask on sensitive data before share.
Regards,
Xiaoxin Sheng
Helper IIIHi Xiaoxin,
The 'DATA' table below contains data for 5 sensors over 6 days
| Date | Sensor Number | Occupied |
| 01/01/2019 | Sensor 1 | 1 |
| 01/01/2019 | Sensor 2 | 0 |
| 01/01/2019 | Sensor 3 | 1 |
| 01/01/2019 | Sensor 4 | 0 |
| 01/01/2019 | Sensor 5 | 1 |
| 02/01/2019 | Sensor 1 | 0 |
| 02/01/2019 | Sensor 2 | 0 |
| 02/01/2019 | Sensor 3 | 0 |
| 02/01/2019 | Sensor 4 | 0 |
| 02/01/2019 | Sensor 5 | 1 |
| 03/01/2019 | Sensor 1 | 1 |
| 03/01/2019 | Sensor 2 | 1 |
| 03/01/2019 | Sensor 3 | 0 |
| 03/01/2019 | Sensor 4 | 0 |
| 03/01/2019 | Sensor 5 | 1 |
| 04/01/2019 | Sensor 1 | 0 |
| 04/01/2019 | Sensor 2 | 1 |
| 04/01/2019 | Sensor 3 | 0 |
| 04/01/2019 | Sensor 4 | 1 |
| 04/01/2019 | Sensor 5 | 1 |
| 05/01/2019 | Sensor 1 | 0 |
| 05/01/2019 | Sensor 2 | 1 |
| 05/01/2019 | Sensor 3 | 0 |
| 05/01/2019 | Sensor 4 | 1 |
| 05/01/2019 | Sensor 5 | 0 |
| 06/01/2019 | Sensor 1 | 0 |
| 06/01/2019 | Sensor 2 | 1 |
| 06/01/2019 | Sensor 3 | 0 |
| 06/01/2019 | Sensor 4 | 1 |
| 06/01/2019 | Sensor 5 | 0 |
I have two measures;
Occupied Days = SUM('DATA' [Occupied])
Occupied % = DATA[Occupied Days]/DISTINCTCOUNT('DATA' [Date])
From these i can produce a table as below. This table works with the date slicer on the report
| Sensor Number | Occupied Days | Occupied % |
| Sensor 1 | 2 | 33.33% |
| Sensor 2 | 4 | 66.67% |
| Sensor 3 | 1 | 16.67% |
| Sensor 4 | 3 | 50.00% |
| Sensor 5 | 4 | 66.67% |
What i am trying to do is generate the table below that also works with the date slicer, but i can't seem to get close and i cant find any solutions in the forum. Please help if possible.
| Range | Count |
| 0%-20% | 1 |
| 20%-40% | 1 |
| 40%-60% | 1 |
| 60%-80% | 2 |
| 80%-100% | 0 |
Thanks
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
- PaulHallam
All working great thanks for your help...!
- PaulHallam
Hi v-shex-msft
Thank you for your help before, i need to so something similar and am applying the same logic as you have given for the previous solution.
I need to produce a graph as below showing the number of occurances of each count of occupied. From the previous data the graph would beThe number of occurances will differ massively so i cant just add a table as before and the graph needs to be updated by slicers.
Thanks in advance, P- PaulHallam
The axis are the other way round. My mistake