Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Including 0 values when calculating STDEV

Hi, Let me walk you through a better example.  I am attempting to determine the most recent 6-Month STDEV for sales by product in order to determine variation.     The Sales Data is made up of dai...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    Anonymous Maybe something like this, PBIX is attached below signature. I'm not getting your stated standard deviation values but I added a debugging measure into the PBIX that should help you determine what is going on. With the debugging measure you can see that it is returning 0's for empty months.

    Measure StdDev = 
    VAR __Date = MAX('Table'[Date])
    VAR __BeginDate = EOMONTH(__Date,-6)
    VAR __EndDate = EOMONTH(__Date, -1)
    VAR __Table = FILTER(ALL('Table'),[Date]>=__BeginDate && [Date]<=__EndDate)
    VAR __Count = COUNTROWS(__Table)
    VAR __Missing = 6 - __Count
    VAR __MissingTable = 
        SELECTCOLUMNS(
            ADDCOLUMNS(
                GENERATESERIES(1,__Missing,1),
                "rmv",0
            ),
            "rmv",[rmv],
            "Date",[Value]
        )
    VAR __FinalTable = UNION(__Table, __MissingTable)
    VAR __Result = STDEVX.P(__FinalTable,[rmv])
RETURN
    __Result

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

Anonymous Maybe something like this, PBIX is attached below signature. I'm not getting your stated standard deviation values but I added a debugging measure into the PBIX that should help you determine what is going on. With the debugging measure you can see that it is returning 0's for empty months.

Measure StdDev = 
    VAR __Date = MAX('Table'[Date])
    VAR __BeginDate = EOMONTH(__Date,-6)
    VAR __EndDate = EOMONTH(__Date, -1)
    VAR __Table = FILTER(ALL('Table'),[Date]>=__BeginDate && [Date]<=__EndDate)
    VAR __Count = COUNTROWS(__Table)
    VAR __Missing = 6 - __Count
    VAR __MissingTable = 
        SELECTCOLUMNS(
            ADDCOLUMNS(
                GENERATESERIES(1,__Missing,1),
                "rmv",0
            ),
            "rmv",[rmv],
            "Date",[Value]
        )
    VAR __FinalTable = UNION(__Table, __MissingTable)
    VAR __Result = STDEVX.P(__FinalTable,[rmv])
RETURN
    __Result

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thanks Greg_Deckler ,

 Apologies for making things difficult for you Greg_Deckler . I don't see any option to upload the sample file. There is a bit change in dataset and sorry for that. 

when I calculated Standard Deviation Manually for the past 21 Months(Dec 2017 selected in the Slicer) I got the Standard Deviation as 0.94.

Note: I think I can't afford to create a calculated table, the table is transactional table it's keep growing.

 

Thanks

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Anonymous I didn't create a calculated table, just a measure. You can share a PBIX file on something like OneDrive or Box and post a link here.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Greg_Deckler ,

       

      Unfortunately, I can't share my work ondrive link over here, If possible could you please use with the above pasted data.

      sorry for the trouble.

       

      Thanks

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        Anonymous The problem with what you shared is I don't see any data to take a standard deviation of. What am I missing?

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        Anonymous So the Data table should be this?

        Date rmv
        Jun-20 3
        Sep-20 13
        Oct-20 1
        Nov-20 5
        Dec-20 7
        Jan-21 6
        Feb-21 3
        Mar-21 4
        Apr-21 7
        May-21 1
        Jun-21 4
        Jul-21 3
        Aug-21 1
        Sep-21 2
        Oct-21 1
        Nov-21 1
        Dec-21 4
        Jan-22 7