Forum Discussion

PaulHallam's avatar
PaulHallam
Icon for Helper III rankHelper III
7 years ago
Solved

Count values assigned into a range inside a measure

Hi All,

I have a table 'DATA' as below;

 

DateSensor NumberOccupied
01/01/2019Sensor 11
01/01/2019Sensor 20
01/01/2019Sensor 31
01/01/2019Sensor 40
01/01/2019Sensor 51
02/01/2019Sensor 10
02/01/2019Sensor 20
02/01/2019Sensor 30
02/01/2019Sensor 40
02/01/2019Sensor 51
03/01/2019Sensor 11
03/01/2019Sensor 21
03/01/2019Sensor 30
03/01/2019Sensor 40
03/01/2019Sensor 51
04/01/2019Sensor 10
04/01/2019Sensor 21
04/01/2019Sensor 30
04/01/2019Sensor 41
04/01/2019Sensor 51
05/01/2019Sensor 10
05/01/2019Sensor 21
05/01/2019Sensor 30
05/01/2019Sensor 41
05/01/2019Sensor 50
06/01/2019Sensor 10
06/01/2019Sensor 21
06/01/2019Sensor 30
06/01/2019Sensor 41
06/01/2019Sensor 50

 

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;

Sensor NumberOccupied DaysOccupied %
Sensor 1233.33%
Sensor 2466.67%
Sensor 3116.67%
Sensor 4350.00%
Sensor 5466.67%

 

What i am trying to do is generate the table below, but i can't seem to get close and i cant find any solutions in the forum. Please help if possible.

 

RangeCount
0%-20%1
20%-40%1
40%-60%1
60%-80%2
80%-100%0


Thanks for any pointers in the right direction.

General Comment
Need Help
Tips and Tricks

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Maybe this can help?

     

    First, created a range table with min, max and a lable to use.  Called RangeTable

     

    Added a calculated column to your original table that will bring in that range label

    Range = 
CALCULATE(
    VALUES(RangeTable[Range Label]),
    FILTER(
        RangeTable,
        Table1[Occupied %] >= RangeTable[Min]
        && Table1[Occupied %] < RangeTable[Max]
    )
)

     

    Then depending on what your end goal is, you can create a summarized table or use the code in a measure for a virtual table

    Table = ADDCOLUMNS( SUMMARIZE( Table1, Table1[Range]),"Count", CALCULATE( COUNTROWS(Table1)))