topic Re: MEDIANX missing values for dates with no data in DAX Commands and Tips

MEDIANX missing values for dates with no data

DRA885 — Wed, 25 Jan 2023 04:14:48 GMT

Hello all.

I'm looking for some help on this DAX code:

Median Test 3 (next 3 months approved) = VAR MedianValueDiscipline = CALCULATE ( MEDIANX ( SUMMARIZE ( 'Actual & Forecast Project Resources', 'Actual & Forecast Project Resources'[Work Week Ending Date], 'Actual & Forecast Project Resources'[Capsule Descriptions.Discipline], "Median FTEs", SUMX ( 'Actual & Forecast Project Resources', 'Actual & Forecast Project Resources'[Consolidated FTEs] ) ), [Median FTEs] ), 'Actual & Forecast Project Resources'[Final Consolidated Hours Type] = "Approved", DATESINPERIOD ( 'Actual & Forecast Project Resources'[Work Week Ending Date], MAX ( 'Date'[EndOfThisWeek] ) - 1, 3, MONTH ) ) RETURN MedianValueDiscipline

This is producing the following results in a matrix:

Whilst this is giving the correct total value, the rows with null values are incorrect as the Median is only calculating for dates where there is a value. The rows with 'blanks' are from the field 'Actual & Forecast Project Resources'[Capsule Descriptions.Level Category]

My question is, how can I force zero values into the 'blanks' and obtain the correct result for each row of the matrix?

TIA, Ian

Re: MEDIANX missing values for dates with no data

DRA885 — Thu, 26 Jan 2023 23:33:31 GMT

solved with the following change to the Sum calculation of FTEs:

Median Test 3 (next 3 months approved) =

VAR MedianValueDiscipline =

CALCULATE (

MEDIANX (

SUMMARIZE (

'Actual & Forecast Project Resources',

'Actual & Forecast Project Resources'[Work Week Ending Date],

'Actual & Forecast Project Resources'[Capsule Descriptions.Discipline],

"Median FTEs",

IF (

CALCULATE (

SUM ( 'Actual & Forecast Project Resources'[Consolidated FTEs] ),

'Actual & Forecast Project Resources'[Final Consolidated Hours Type] = "Approved"

) > 0,

CALCULATE (

SUM ( 'Actual & Forecast Project Resources'[Consolidated FTEs] ),

'Actual & Forecast Project Resources'[Final Consolidated Hours Type] = "Approved"

)

[Median FTEs]

DATESINPERIOD (

'Actual & Forecast Project Resources'[Work Week Ending Date],

MAX ( 'Date'[EndOfThisWeek] ) - 1,

MONTH

)

RETURN

MedianValueDiscipline

Re: MEDIANX missing values for dates with no data

DRA885 — Thu, 02 Feb 2023 00:59:33 GMT

Sorry thought I had the solution but this is still incorrect.
Obviously now still looking for a solution to this

Re: MEDIANX missing values for dates with no data

Anonymous — Wed, 03 Jan 2024 08:35:40 GMT

DRA885 , did you manage to solve this? I am currently fasing the same problem.