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!