Forum Discussion

C-Jac's avatar
C-Jac
Icon for Helper I rankHelper I
5 years ago
Solved

Use measure in calculated row for multiple rows

I have a table which looks like this:    Value:  AbsValue:    InLast7Days:    IsSignificant:    500 500 1 1 -600 600 1 1 -8,000 8,000 0 0 25,000    25,000 0 0 40 ...
Calculated columns
DAX
measure
Standard Deviation
  • rfigtree's avatar
    rfigtree
    5 years ago

    hope this helps.

    When performing the STD calculation you need to tell CALCULATE which context you want to consider.

    The context you described is all rows where the last seven days flag is one.

    It can be a bit of a mind bender, but as grasped is amazingly powerful.

     

    =var _STD=CALCULATE(STDEV.P(Table1[Value]),
                    filter(ALL(Table1),[InLast7Days]=1)
                    )
return if(Table1[AbsValue]>_STD && 
          Table1[InLast7Days]=1,
          1,0)

     

     

rfigtree's avatar
rfigtree
Icon for Resolver III rankResolver III
5 years ago

hope this helps.

When performing the STD calculation you need to tell CALCULATE which context you want to consider.

The context you described is all rows where the last seven days flag is one.

It can be a bit of a mind bender, but as grasped is amazingly powerful.

 

=var _STD=CALCULATE(STDEV.P(Table1[Value]),
                    filter(ALL(Table1),[InLast7Days]=1)
                    )
return if(Table1[AbsValue]>_STD && 
          Table1[InLast7Days]=1,
          1,0)

 

 

C-Jac's avatar
C-Jac
Icon for Helper I rankHelper I
5 years ago

This works so well, thank you so much! 
If i would like to add an additional filter to the _STD calculation, like an EstimateType which could be 1, 2 or 3, could I just add it like this?

=var _STD=CALCULATE(STDEV.P(Table1[Value]),
                    filter(ALL(Table1),[InLast7Days]=1)
                    , [EstimateType] = 2)


Or would I have to add it in a different way?