Created a measure for # of submissions, now how do I create a calculated column out of it per month?

[Anonymous]

Hi!

So what I'm trying to do ultimately is find the standard deviation of a measure. I have a measure called submissions, that calculates the # of submissions for a counting program made based on COUNT of every observation in the dataset, but when I use stdx.p etc. on it directly, on the submissions measure, it always returns 0. I believe this is because there is only one value being counted, probably the total # of submissions for the entire dataset. However, I want to look at submissions per each month (also broken down by year) for example, there were 261 submissions in January 2021 etc. Ultimately in the finished graphs and tables, I will be slicing by station also. For example Station X had 261 Submissions in January 2021, 450 submissions in February 2021, etc.

So ultimately, I want the standard deviation by station across all the months for submissions in a finished graph.

My best idea of how to get there is to somehow get the submissions per month in a column, rather than a measure, for example like they have already been calculated by month. Or perhaps creating a temporary table with submissions per month, and using that to get the standard deviation somehow. I am a bit lost asto how to get this or if I am even on the right track, and would greatly appreciate some guidance, and lots of detail about how I can get to the results I want.

Thanks so much in advance!

Max

[AilleryO]

Hi,

It should be something like :

SUMMARIZE calc = SUMMARIZECOLUMNS( table[Year Month] , //Group by Year Month
"Name of new column" , [YourMeasure])

Tell us...

[AilleryO]

Hi,

I would suggest the preconsolidated table, so your further calculation will be faster than if you have the whole thing to calculate.

To create your preconsolidated table, you can use functions like SUMMARIZECOLUMNS. If you do not have a column with year month, you can create it using FORMAT function : 

FORMAT ( [Date] , "yyyy - mm" )

and use ti to summarize your values, then it will be easu=y to calculate your deviation.

If you really need Time Intelligence functions, you will require a Date Table.

Hope it helps

[Anonymous]

Hi,

Thanks so much for the help! So I made the year-month column, and now I am trying to figure out how to do the summarize columns correctly. So I am starting with my measure of submissions, which is coded by COUNT(Observation) basically. So I have not been able to figure out how to get the measure into the SUMMARIZE Columns expression, I've only been able to figure out how to get it to take other columns. I'm trying to basically count every observation by individual month. Do you know how I would code the summarize columns expression?

Thanks!