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.

     

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.