Average and Sum in one DAX Measures based on row context

[Anonymous]

Hi All,
I am trying to achieve the values in the expected result column where I want to have an average of Values column where metric is headcount but sum of Values where metric on the row is Sales and Profit?

Please help, example is given below.

Two questions;
1) How can it be achieved it in clculated column?
2) How can it be achieved through DAX meaure using VAR ?

[smpa01 · Accepted solution]

@Anonymous 

Column =
IF (
    'Table'[Metric] = "Head Count",
    CALCULATE ( AVERAGE ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Metric] ) ),
    CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Metric] ) )
)

[Anonymous · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment), please check whether that is what you want. You can create a calculated column or measure as below to get the values:

1. Create a calculated column just as suggested by @smpa01 

Column = 
IF (
    'Table'[Metric] = "Head count",
    CALCULATE (
        AVERAGE ( 'Table'[Values] ),
        ALLEXCEPT ( 'Table', 'Table'[Metric] )
    ),
    CALCULATE ( SUM ( 'Table'[Values] ), ALLEXCEPT ( 'Table', 'Table'[Metric] ) )
)

2. Create a measure as below

Measure = 
VAR _selmetric =
    SELECTEDVALUE ( 'Table'[Metric] )
VAR _average =
    AVERAGEX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Metric] = _selmetric ),
        [Values]
    )
VAR _sum =
    SUMX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Metric] = _selmetric ),
        [Values]
    )
RETURN
    IF ( _selmetric = "Head count", _average, _sum )

Best Regards

[smpa01 · Accepted solution]

@Anonymous 

Column =
IF (
    'Table'[Metric] = "Head Count",
    CALCULATE ( AVERAGE ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Metric] ) ),
    CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Metric] ) )
)