Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

combine multiple expression into one CALCULATE function

Dear Community, I want to show AVERAGE and STANDARD DEVIATION, based on below measure I can show AVG, but when i am trying to add STDV, got error, (A function 'STDEV.S' has been used in a True/False expression that is used as a table filter expression. This is not allowed.)
is there a way to show this two with one CALCULATE func,?
or should i separate them? how? 

MeasureStdvY =
IF(
ISINSCOPE('M'[D]),
SUM('M'[Y]),
CALCULATE(
AVERAGE('wash M'[Y]),
STDEV.S('wash M'[Y]),
ALLEXCEPT('S','S'[C],'S'[S], 'S'[L])
)
)
General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • Icey's avatar
    Icey
    5 years ago

    Hi Anonymous ,

     

    You can also try this:

    MeasureStdvY =
VAR Avg_ =
    CALCULATE ( AVERAGE ( 'wash M'[Y] ), ALLEXCEPT ( 'S', 'S'[C], 'S'[S], 'S'[L] ) )
VAR Stdev_ =
    CALCULATE ( STDEV.S ( 'wash M'[Y] ), ALLEXCEPT ( 'S', 'S'[C], 'S'[S], 'S'[L] ) )
RETURN
    IF ( ISINSCOPE ( 'M'[D] ), SUM ( 'M'[Y] ), Avg_ & ", " & Stdev_ )

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.

4 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    @Helia9235

    You can only display one value per column, unless you present them as text with something like:

    [MeasureAvgY] &  "-" &  [MeasureStdvY]

    Please mark the resolved question when you are finished and consider giving a thumbs up if the posts are useful.

    Contact me privately for assistance with any large-scale BI needs, tutoring, etc.

    Bless you

    SU18_powerbi_badge

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous 

    What do you exactly mean by showing the two? The sum?

    If you want the separate results you need two separate measures:

    MeasureAvgY =
IF (
    ISINSCOPE ( 'M'[D] ),
    SUM ( 'M'[Y] ),
    CALCULATE (
        AVERAGE ( 'wash M'[Y] ),
        ALLEXCEPT ( 'S', 'S'[C], 'S'[S], 'S'[L] )
    )
)

     

    MeasureStdvY =
IF (
    ISINSCOPE ( 'M'[D] ),
    SUM ( 'M'[Y] ),
    CALCULATE (
        STDEV.S ( 'wash M'[Y] ),
        ALLEXCEPT ( 'S', 'S'[C], 'S'[S], 'S'[L] )
    )
)

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi AlB ,
      I want to show both STEV and AVG in one column,Is this possible ? , I do not want to calculate them separately then I will have 2 different measure as you wrote.

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    You can also try this:

    MeasureStdvY =
VAR Avg_ =
    CALCULATE ( AVERAGE ( 'wash M'[Y] ), ALLEXCEPT ( 'S', 'S'[C], 'S'[S], 'S'[L] ) )
VAR Stdev_ =
    CALCULATE ( STDEV.S ( 'wash M'[Y] ), ALLEXCEPT ( 'S', 'S'[C], 'S'[S], 'S'[L] ) )
RETURN
    IF ( ISINSCOPE ( 'M'[D] ), SUM ( 'M'[Y] ), Avg_ & ", " & Stdev_ )

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.