Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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] ) ) ) + 0
Regards,
Xiaoxin Sheng
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
Thanks 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 Paul
Hi @PaulHallam ,
Can you please share some sample data for test? Please do mask on sensitive data before share.
Regards,
Xiaoxin Sheng
Hi 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] ) ) ) + 0
Regards,
Xiaoxin Sheng
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 be
The 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
The axis are the other way round. My mistake
All working great thanks for your help...!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |