Calling a Column from a SUMMARIZE() Function

[j_ocean]

I have data down to the daily level, reporting monthly sums. Easy enough. The problem is I'd like to get an average and standard deviation at the quarterly level, based on the monthly. I was able to do it with a calculated table built with SUMMARIZE(), but calculated tables only refresh when the model does so the result isn't dynamic with filters on any other categorical data columns. 

SUMMARIZE() results in a table, but as a measure I can't get STDEV.P or STDEVX.P to recognize that it *is* a table and let me speficy the column to do the function on. It just throws the error that it needs a single column. How do i call just that one column?

[danextian]

HI @j_ocean ,

If you want the calculation to be  dynamic, the approach would be to use a virtual table in  a measure instead of using physical tables which update only upon data refresh or when the formula changes and then use the X functions  to aggregate the values within that virtual table.
Below sample formula summarizes the measure Total by [Date] in Dates table. Please note that the fact table could have more than one row for each date. AVERAGEX is then used to get the average of Total_ column within the virtual summary table.

Daily Average (Summarize) = 
AVERAGEX (
    ADDCOLUMNS ( SUMMARIZE ( Dates, Dates[Date] ), "Total_", [Total] ),
    [Total_]
)

 
I would have given a sample formula for your use case but it isn't clear what you mean by "standard deviation at the quarterly level, based on the monthly". You also mentioned average but average of what?

It is always a good idea to provide a sample data (not an image) and from that sample data the expected result.

[j_ocean]

Thank you. For those who may read this later and to clarify, while the incoming data is daily the dashbaord I'm building is for monthly sum, thus sum, average, and standard deviation need to be on the already-summed monthly number. Since there's a number of categorical columns that the user may need to filter on *and* other tabs do need daily data, I couldn't just create a pre-aggregated fact table in Power Query.

I was able to get it to work with the below. The sticking point was that column call on the virtual table built with SUMMARIZE() wasn't popping up as a column but just manually typing "[Total]" worked.

CALCULATE(

    STDEVX.P(

        SUMMARIZE(

            Sales,

            'Calendar'[Yr Qtr Month],

            "Total",

            SUM(Sales[Transaction Value])

        ),

        [Total]

    ),

    FILTER(

        ALLSELECTED('Calendar'),

        SELECTEDVALUE('Calendar'[YR Qtr]) = 'Calendar'[YR Qtr]

    )

)