topic Re: Average and Sum in one DAX Measures based on row context in DAX Commands and Tips

Average and Sum in one DAX Measures based on row context

Anonymous — Sun, 19 Dec 2021 12:42:33 GMT

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 ?

Re: Average and Sum in one DAX Measures based on row context

smpa01 — Sun, 19 Dec 2021 13:10:30 GMT

Anonymous

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

Re: Average and Sum in one DAX Measures based on row context

Anonymous — Wed, 22 Dec 2021 02:44:26 GMT

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