Grouping data from calculated columns

[HopkiJ]

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!

Date	Office Identifier	Person Identifier	Present
08.08.2024	LDN1	ABC	1
08.08.2024	GLA2	XYZ	1

[Anonymous]

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]

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 · Accepted solution]

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]

Thanks Clara, that worked a dream!

[rajendraongole1]

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]

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.