combine multiple expression into one CALCULATE function

[Anonymous]

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])

)

)

[Icey · Accepted solution]

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.

[AlB]

@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

[AlB]

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]

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.