Thank you all for your comments. I have tried these suggestions, but it does appear that these still only help with creating a 0 for blank data not with including the 0 months in the standard deviation calculation. My issue lies there. I am actually not visualizing or reporting the standard deviation. I am using it in subsequent calculations to chart year over year control limits based on prior year monthly rates. Because the mean for the control limits includes the zero months, the standard deviation to calculate the control limits should also include the zero months. I did try putting the +0 within the statement and the if isblank within the statement neither with success.
stdev = VAR __table = SUMMARIZE(Transfers,'*Calendar'[FYFM],"StDev__Measure",[Transfer Rate]+0)
RETURN STDEVX.P(__table,[Transfer Rate]+0)
stdev = VAR __table = SUMMARIZE(Transfers,'*Calendar'[FYFM],"StDev__Measure",IF(ISBLANK([Total Transfers]),0,[Transfer Rate]))
RETURN STDEVX.P(__table,[Transfer Rate])
------------------------------
Jenny Lane
Sr. Human Resources Analyst
------------------------------
Original Message:
Sent: Jul 10, 2019 05:33 AM
From: Vishesh Jain
Subject: Including zeros in calculations for months with no data
Hi @Jenny Lane,
Apart from the solution mentioned above, you can even try to click on the year-month column in the bucket of the visual, by which you are trying to show the values, and enable 'Show items with no data'
This will display the blanks as well in your visual.
Hope this helps.
Thank you,
------------------------------
Vishesh Jain
Owner
VR Construction
Original Message:
Sent: Jul 10, 2019 03:48 AM
From: Miguel Félix
Subject: Including zeros in calculations for months with no data
Hi @Jenny Lane,
Just add a + 0 to the end of your measure so you will end up with something like this:
Transfer Rate = DIVIDE([Total Transfers],[Average Employees])+0stdev = VAR __table = SUMMARIZE(Transfers,'*Calendar'[FYFM],"StDev__Measure",[Transfer Rate])RETURN STDEVX.S(__table,[Transfer Rate]) + 0
Regards,
MFelix
------------------------------
Miguel Félix
Business analist
Original Message:
Sent: Jul 09, 2019 03:44 PM
From: Jenny Lane
Subject: Including zeros in calculations for months with no data
I am trying to calculate the standard deviations across months, however it is only calculating for months with data. I tried adding the +0 to the calculation for the transfer rate measure which did populate zeros for no activity months when looking at the transfer rate, however it is still not using the zeros in the standard deviation calculation. It is calculating correctly if I wished to include only months with data (rate>0%).
These are the two measures that I am using. FYFM is in my calendar table and is the fiscal year fiscal month combination. The calendar table is related to the Transfers and Employees table by date. Any thoughts? Thank you!
Transfer Rate = DIVIDE([Total Transfers],[Average Employees])+0
stdev = VAR __table = SUMMARIZE(Transfers,'*Calendar'[FYFM],"StDev__Measure",[Transfer Rate])
RETURN STDEVX.S(__table,[Transfer Rate])
------------------------------
Jenny Lane
Sr. Human Resources Analyst
------------------------------