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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi PaulHallam ,

I'd like to suggest you do unpivot columns on your time fields to convert them to time attribute and value.

Unpivot columns (Power Query)

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

  • 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