Forum Discussion
Calling a Column from a SUMMARIZE() Function
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.
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.