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

Resources