Create sums as per type and value

[ShantanuDagar]

       ID       Type       Date      Quarter       Year       Value        Count of Auto More than 100 as per quarter         Count of Auto less than 100 as per Q    Count of Fixed more than 100 as per Q         Count of Fixed < 100 as per Q

AB121212 Auto  2023-08-11     3             2023       200
BC121221 Auto  2023-08-16     3            2023        400
AC121212 Fixed  2023-08-12    3            2023        50
BD121221 Fixed  2023-09-16    3            2023        83

AB121212 Auto  2023-01-11      1            2024        130
BC121221 Auto  2023-01-16     1            2024         260
AC121212 Fixed  2023-04-11    2           2024          53
BD121221 Fixed  2023-05-16    2           2024         110

Hi, the above is sample data until value column, rest count ones aren't part of sample data.

But I want the count of IDs more than 100 and less than 100 for each Quarter in a specific year as per the type as a column preferably (I want to use it as a variable in report text to draft an email)

So, for example, Count of Auto More than 100 in Quarter 3 year 2023 =2

Count of Auto More than 100 in Quarter 4 year 2023 =0

Count of Auto More than 100 in Quarter 1 year 2024 =2

Count of Auto less than 100 in Quarter 3 year 2023 =0

Count of Auto less than 100 in Quarter 4 year 2023 =0

Count of Auto less than 100 in Quarter 1 year 2024 =0

Similarly for Fixed, count more than and less than in each quarter.

And the data expands over multiple quarters.

Thx

[Prometheus · Accepted solution]

@ShantanuDagar First, I used a Formula tool to create the counts, then a Summarize tool to total the counts by Type, Quarter, and Year. Then I used the Transpose tool to create a field for the type of count and a field for the number of counts.

 

 

 

[ShantanuDagar]

Works great!