Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.

View My Drafts

Avanade - November 2020
Back to discussions
Expand all | Collapse all

Including zeros in calculations for months with no data

  • 1.  Including zeros in calculations for months with no data

    Posted Jul 09, 2019 03:45 PM
    ​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
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Including zeros in calculations for months with no data

    Posted Jul 10, 2019 02:08 AM
    ​
    Try wrapping an...

    if(isblank(....., 0))

    ... around your first measure.

    Shane

    ------------------------------
    Shane Glasheen
    Data Scientist
    +6163698141
    ------------------------------

    Original Message
    Academy - Online Interactive Learning from Experts


  • 3.  RE: Including zeros in calculations for months with no data

    Silver Contributor
    Posted Jul 10, 2019 03:49 AM
    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])+0



stdev = 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
    Academy - Online Interactive Learning from Experts


  • 4.  RE: Including zeros in calculations for months with no data

    Top Contributor
    Posted Jul 10, 2019 05:34 AM
    Edited by Vishesh Jain Jul 10, 2019 05:34 AM
    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
    Academy - Online Interactive Learning from Experts


  • 5.  RE: Including zeros in calculations for months with no data

    Posted Jul 10, 2019 09:41 AM
    ​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
    Academy - Online Interactive Learning from Experts


  • 6.  RE: Including zeros in calculations for months with no data

    Posted Jul 10, 2019 07:04 PM
    ​
    Not sure if the following syntax is allowed, but how about putting the isblank here.....

    RETURN isblank(STDEVX.P(__table,[Transfer Rate]),0)

    Shane

    ------------------------------
    Shane Glasheen
    Data Scientist
    +6163698141
    ------------------------------

    Original Message
    Academy - Online Interactive Learning from Experts


  • 7.  RE: Including zeros in calculations for months with no data

    Top Contributor
    Posted Jul 11, 2019 05:33 AM
    Hi @Jenny Lane,

    Do you mind sharing your file with us along with the same result/visual that you are looking for?

    Thank you,​​

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Original Message
    Academy - Online Interactive Learning from Experts


  • 8.  RE: Including zeros in calculations for months with no data

    Top Contributor
    Posted Jul 11, 2019 01:45 PM
    Hi @Jenny Lane,

    Are there entries in the Transfers table for months where there are no transfers?  If not then __Table you create in your measure will have no entry for that month so the zero Transfer Rate won't be there.  To get the zero months into that table you need to summarize across the *Calendar table.  I don't think you need the SUMMARIZE either, just a list of FYFM values:

    Stdev by Month =
    VAR __table = DISTINCT('*Calendar'[FYFM])
    RETURN STDEVX.S(__table,[Transfer Rate])

    Hope that works for you.
    ​

    ------------------------------
    Simon Lamb
    IT Consultant, Method Excel Ltd.
    ------------------------------

    Original Message
    Academy - Online Interactive Learning from Experts


  • 9.  RE: Including zeros in calculations for months with no data

    Posted Jul 11, 2019 03:25 PM
    ​Getting rid of the summarize function did work. Thank you!!! Always something simple.

    ------------------------------
    Jenny Lane
    Sr. Human Resources Analyst
    ------------------------------

    Original Message
    Academy - Online Interactive Learning from Experts


  • 10.  RE: Including zeros in calculations for months with no data

    Posted Jul 11, 2019 03:56 PM
    Forgot to add that I did still have to include the +0 either in the transfer rate measure or the stdev measure. I decided to do it in the stdev measure.

    stdev = VAR __table = DISTINCT('*Calendar'[FYFM])
RETURN STDEVX.P(__table,[Transfer Rate]+0)​


    ------------------------------
    Jenny Lane
    Sr. Human Resources Analyst
    540-759-4145
    ------------------------------

    Original Message
    Academy - Online Interactive Learning from Experts


  • 11.  RE: Including zeros in calculations for months with no data

    Top Contributor
    Posted Jul 13, 2019 07:49 AM
    Jenny,

    Have you considered the STDEVX.P formula is wired to ignore zeros?  I am not sure, but it seems to be the case.  I would consider manually calculating the standard deviation.  A google search would provide easy steps to do this.  If it were me, i would do this in the data model on ETL (Using M in the query to add the necessary columns).  This would give you a quick check and allow you to include the records with zero rates.

    ------------------------------
    John Thomas
    Director Project Controls
    Huntersville NC
    2103550035
    ------------------------------

    Original Message
    Academy - Online Interactive Learning from Experts