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 visitors and how many are infrequent attendees. This needs to be in buckets of 1-5 days per month, 6-10 days per month, etc. 

 

I have so far tried:

 

TotalVisits = 
CALCULATE(
    SUM('Table'[Present]),
    DATESBETWEEN('Date Table'[Dates], MIN('Date Table'[Dates]), MAX('Date Table'[Dates]))<p>Then:</p><p> <li-code lang="markup">Groups = switch(True(), 
[TotalVisits] <= 5, "1-5",
    IF(
        [TotalVisits] <= 10, "6-10",
        IF(
        [TotalVisits] <= 15, "11-15",
        "15+"
    )
))

 

 

 

However, the "Groups" will not show up on any visuals axis and only works in tables as an additional column. I really need to slice this data via the buckets above, any help would be greatly appreciated!

 

DateOffice IdentifierPerson IdentifierPresent
08.08.2024LDN1ABC1
08.08.2024GLA2XYZ1
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.

     

6 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi HopkiJ - Calculate the total number of visits per person for each month.

    TotalVisitsPerMonth =
    CALCULATE(
    COUNTROWS('Table'),
    ALLEXCEPT('Table', 'Table'[Person Identifier], 'Date Table'[Year-Month])
    )

     

    hope you already have a date table as mentioned formaule above, if not please create and add below new column

    Year-Month = FORMAT([Dates], "YYYY-MM")

     

    Next create another calculated column or measure to categorize these visits into your defined buckets like example

    VisitGroup =
    SWITCH(
    TRUE(),
    [TotalVisitsPerMonth] <= 5, "1-5",
    [TotalVisitsPerMonth] <= 10, "6-10",
    [TotalVisitsPerMonth] <= 15, "11-15",
    "15+"
    )

    Now drag the visitgroup column in slicer and your visual (chart). 

     

    it works

     

     

    • HopkiJ's avatar
      HopkiJ
      Frequent Visitor
      2 years ago

      Hi Rajendraongole1, 

      Thank you fo ryour suggestion, this is imilar to others I have been attempting. However, this formula returns the bucket 1-5 for all lines which definitely isn't correct. 

       

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