Forum Discussion

MindIsBlank's avatar
MindIsBlank
Frequent Visitor
6 years ago
Solved

DAX Version of Excel's DSTDEV

New to Power BI but have some experience with Tableau.   Looking to add a measure that calculates the standard deviation for unique categorical values in a column. For example, Column A has a categ...
Need Help
  • MindIsBlank's avatar
    MindIsBlank
    6 years ago

    I figured it out. Had to use nested filtering described in this article from 2012.

     

    Measure code looks like this:

     

    calculate(
         STDEV.P(Column B)
         ,FILTER(
              ALL('Table')
              ,COUNTROWS(
                   FILTER(
                        'Table'
                        ,EARLIER(Column A) = Column A
                   )
              )
         )
    )

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Measure =

Calculate (

        STDEV.P('TableName'[ColumnB]),

        ColumnA = "Name1")

MindIsBlank's avatar
MindIsBlank
Frequent Visitor
6 years ago

Yeah, I thought of that but I need it to dynamically load what goes into the CALCULATE() filter based on the row Power BI is evaluating. So it would be something like:

 

Calculate (

        STDEV.P('TableName'[ColumnB]),

        ColumnA = {Value from Column A in row being evaluated})

 

I just can't figure out how to tell it to do it that way. Another way I've gotten this to work in Excel is using the following formula as an array formula.

 

=STDEV.P(IF($A$2:$A$9000=A2,$B$2:$B$9000))

  • MindIsBlank's avatar
    MindIsBlank
    Frequent Visitor
    6 years ago

    I figured it out. Had to use nested filtering described in this article from 2012.

     

    Measure code looks like this:

     

    calculate(
         STDEV.P(Column B)
         ,FILTER(
              ALL('Table')
              ,COUNTROWS(
                   FILTER(
                        'Table'
                        ,EARLIER(Column A) = Column A
                   )
              )
         )
    )