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:  ...
excel
Formulas and Functions
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