How to use a pivot to work out counts

Brass Contributor
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

1 Reply

@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