How do I return grand total in table reference in a measure?

[tvalente]

Hi All,

I am 90% of the way in a calculation but I'm stuck now... I am summarising a table based on the filter context (if someone filters date in a date slicer). All I want to do is return the grand total of rows. 

This is not what I want:

This is what I want:

This is my DAX so far:

AVG Ticket Volume =

Var MinDate = MIN(Tickets[Date_Created])

Var MaxDate = Max(Tickets[Date_Created])

VAR GrandTotal =

CALCULATE ( COUNT ( Tickets[No] ), ALLSELECTED () )

VAR CountCat =

FILTER (

ADDCOLUMNS (

SUMMARIZE ( 'Tickets', 'Tickets'[Module], 'Dates'[MonthYear] ),

"@Count",

CALCULATE (

COUNT ( Tickets[No] ),

Tickets[Date_Created] >= MinDate

&& Tickets[Date_Created] <= MaxDate

)

),

[@Count] > 0

)

RETURN CALCULATE(COUNTROWS(CountCat))

[NickolajJessen]

I'm having a hard time seeing why you need such complexity in your measure:
Can you tell me why something simple like ALL(Table) wouldn't work?

[tvalente · Accepted solution]

Hi that table is an aggregation of counts by month. So it's not a raw table. I am trying to get a average of counts by month.

I was able to get the numerator correct, but the denominator not. Anyway, I found a solution that has worked as below.

AVG Ticket Volume =

VAR AVGIT =

    CALCULATE (

        AVERAGEX (

            ADDCOLUMNS (

                SUMMARIZE ( Tickets, 'Tickets'[Module], 'Dates'[MonthYear] ),

                "@Count", CALCULATE ( COUNT ( Tickets[No] ) )

            ),

            [@Count]

        ),

        ALLSELECTED ()

    )

RETURN

    AVGIT