Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
DRA885
Frequent Visitor

MEDIANX missing values for dates with no data

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:
Median - next 3 months.png
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
3 REPLIES 3
Anonymous
Not applicable

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

DRA885
Frequent Visitor

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

DRA885
Frequent Visitor

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"
                        ),
                        0
                    )
            ),
            [Median FTEs]
        ),
        DATESINPERIOD (
            'Actual & Forecast Project Resources'[Work Week Ending Date],
            MAX ( 'Date'[EndOfThisWeek] ) - 1,
            3,
            MONTH
        )
    )
RETURN
    MedianValueDiscipline

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.