Forum Discussion

tvalente's avatar
tvalente
Icon for Helper I rankHelper I
4 years ago
Solved

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

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 g...
Need Help
  • tvalente's avatar
    tvalente
    4 years ago

    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

NickolajJessen's avatar
NickolajJessen
Icon for Solution Sage rankSolution Sage
4 years ago

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's avatar
tvalente
Icon for Helper I rankHelper I
4 years ago

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