Forum Discussion

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

Can 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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

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

Thanks for the advice Xiaoxin. I had already tried this and got as far as the following table 'SUMMARY' but then got stuck;

DateSensorOccupied for Day
01/01/2019S11
01/01/2019S21
01/01/2019S31
01/01/2019S41
01/01/2019S51
02/01/2019S11
02/01/2019S21
02/01/2019S31
02/01/2019S40
02/01/2019S51
03/01/2019S10
03/01/2019S20
….….….

 

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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi PaulHallam ,

Can you please share some sample data for test? Please do mask on sensitive data before share.

Regards,

Xiaoxin Sheng

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

    Hi Xiaoxin,

    The 'DATA' table below contains data for 5 sensors over 6 days

    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. This table works with the date slicer on the report 

    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 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.

     

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


    Thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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

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

        All working great thanks for your help...!