Forum Discussion

Booker2401's avatar
Booker2401
Copper Contributor
Feb 14, 2022

Counting the number of times a value occurs in Pivot Table

Hello all!  This is my first question.  I am attempting to count the number of weeks an agent wrote $500 or more in a week.  I have a pivot table that gives each agent and the sum of all premium written in each week.  How can I include a column in the PivotTable to count the number of weeks that total production was >= $500?  See picture below.

 

 

As seen above, the first row should have a total # of >=500 of 1.  the second should be 5, and so on...

 

PLEASE HELP!

Formulas and Functions

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 14, 2022

    Booker2401 

    We may use Total by Rows as such column. Creating PivotTable add data to data model and create measure like

    Weeks:=IF (
    HASONEVALUE ( Table1[Week] ),
    SUM ( Table1[Sales] ),
    COUNTROWS (
        GROUPBY (
            ADDCOLUMNS (
                GROUPBY ( Table1, Table1[Agent], Table1[Week] ),
                "SalesG", CALCULATE ( SUM ( Table1[Sales] ) )
            ),
            Table1[Week],
            "Sales5", SUMX ( CURRENTGROUP (), IF ( [SalesG] > 500, [SalesG], BLANK () ) )
        )
    )
)

    for such sample

  • Martin_Weiss's avatar
    Martin_Weiss
    Bronze Contributor
    Feb 14, 2022

    Hi Booker2401 

     

    you can use a COUNTIF-function.

    Let's assume the first week of your Pivot is in column B and the last in column G and it starts in row 2.

     

    This would be your formula:

    =COUNTIF(B2:G2;">=500")

    It might be, that you need to use , instead of ; depending on your regional settings:

    =COUNTIF(B2:G2,">=500")

Resources