Forum Discussion

HopkiJ's avatar
HopkiJ
Frequent Visitor
2 years ago
Solved

Grouping data from calculated columns

Hi Everyone, I have been really struggling with a query on my data (very small sample below).  I have presence data for when someone is in the office. I want to work out how many people are regular ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi HopkiJ ,

     

    I modified the data and calculated columns.

    Column = 
CALCULATE (
    COUNT ( 'Table'[Person Identifier] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Person Identifier] = EARLIER ( 'Table'[Person Identifier] )
            && 'Table'[Office Identifier] = EARLIER ( 'Table'[Office Identifier] )
            && YEAR ( 'Table'[Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) )
            && MONTH ( 'Table'[Date] ) = MONTH ( EARLIER ( 'Table'[Date] ) )
    )
)

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi HopkiJ ,

 

Based on your description, I created this data.

 

Create a calculated column that counts the number of occurrences per person per month.

Column = 
CALCULATE (
    COUNT ( 'Table'[Person Identifier] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Person Identifier] = EARLIER ( 'Table'[Person Identifier] )
            && YEAR ( 'Table'[Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) )
            && MONTH ( 'Table'[Date] ) = MONTH ( EARLIER ( 'Table'[Date] ) )
    )
)

 

Create calculated columns and group them.

Column2 = 
SWITCH (
    TRUE (),
    'Table'[Column] <= 5, "1-5",
    'Table'[Column] <= 10, "6-10",
    'Table'[Column] <= 15, "11-15",
    "15+"
)

 

 

 

If your Current Period does not refer to this, please clarify in a follow-up reply.

 

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

  • HopkiJ's avatar
    HopkiJ
    Frequent Visitor
    2 years ago

    Hi Clara

     

    That appears to work for the most part, thank you! Do you know how I would get the office identifier to work with the filter as well? 

     

    Many thanks

     

    Jeni 

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi HopkiJ ,

       

      I modified the data and calculated columns.

      Column = 
CALCULATE (
    COUNT ( 'Table'[Person Identifier] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Person Identifier] = EARLIER ( 'Table'[Person Identifier] )
            && 'Table'[Office Identifier] = EARLIER ( 'Table'[Office Identifier] )
            && YEAR ( 'Table'[Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) )
            && MONTH ( 'Table'[Date] ) = MONTH ( EARLIER ( 'Table'[Date] ) )
    )
)

      If your Current Period does not refer to this, please clarify in a follow-up reply.

       

      Best Regards,

      Clara Gong

      If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

       

      • HopkiJ's avatar
        HopkiJ
        Frequent Visitor
        2 years ago

        Thanks Clara, that worked a dream!