Forum Discussion

SGeorgie's avatar
SGeorgie
Brass Contributor
Jan 24, 2023

How to use a pivot to work out counts

Hello,

 

I have data which shows the number of closed tickets per technician and by date among other things.

 

I can pull a pivot to show how many closed tickets per tech but what I need is:

 

Most tickets closed in one day

Fewest tickets closed in one day

Most tickets closed in one day per tech

 

Any ideas please?

 

Thanks

excel
Formulas and Functions

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 24, 2023

    SGeorgie 

    As variant you may

    1) creating PivotTable add data to data model

    2) For the sample as attached create two calculated columns

    Closed this date
=
VAR thisDate = stats[Date]
RETURN
    CALCULATE (
        COUNTROWS ( stats ),
        ALL ( stats ),
        stats[IsClosed] = "yes",
        stats[Date] = thisDate
    ) + 0
////
by Person in Date
=
VAR thisDate = stats[Date]
VAR thisPerson = stats[Person]
RETURN
    CALCULATE (
        COUNTROWS ( stats ),
        ALL ( stats ),
        stats[IsClosed] = "yes",
        stats[Person] = thisPerson,
        stats[Date] = thisDate
    )

    3) add measures

    Max by Person :=
CALCULATE ( MAXX ( stats, stats[by Person in Date] ), ALL ( stats[Date] ) )

Max closed:=MAX( stats[Closed this date] )

Min closed:=MIN( stats[Closed this date] )

    4) return the as PivotTable

Resources