Wrong calculation of the medianX

[konwes97]

Hello everyone, here I am counting weighted averages based on member data. The problem is that if I have empty values then my function takes into account these members and therefore calculations are not correct.

//
MEDIANX(SUMMARIZE('2021','2021'[SMI_CODE],"Comp1 AVG",

CALCULATE(AVERAGE('2021'[COMP3TGT]),'2021'[COMP3TGT]>0)),[Comp1 AVG])

//

As you can see on my code I've applied to count only for member where value is >0.

[JanaP]

Hello,

I am using the formula above, however, some of the values are being ignored and therefore the medianx returns the wrong value. 

I have a 3 tables that is filtered by the user via a slicer. They are copies of the same source, I am using SUMX, AVERAGEX and MEDIANX respectively, all works fine in the first two, only MEDIANX is ignoring some, sometimes all values, depending on the slicer selection.

Here is the formula:

    MEDIANX(

        SUMMARIZE(                                          

            'fct_table',

            'fct_table'[Column_1],        

            'fct_table'[Column_2],                  //primary key for this column is in the slicer, relationship is fine

            "Correct Total",[my_measure]      //my measure is a sum with a filter on the date table

            ),

            [Correct Total]                                 

    )

Where am I going wrong? Why does this work for SUMX and AVERAGEX and not for MEDIANX?

Can somebody help me fix this please?

Thanks a lot, it is driving me mad... 

[Anonymous · Accepted solution]

HI @konwes97,

Perhaps you can try to add this filter on your parameter table to exclude these not match records before calculations:

formula =
MEDIANX (
    SUMMARIZE (
        FILTER ( '2021' , '2021'[COMP3TGT] > 0 ),
        '2021'[SMI_CODE],
        "Comp1 AVG", AVERAGE ( '2021'[COMP3TGT] )
    ),
    [Comp1 AVG]
)

If the above formula does not help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[amitchandak]

@konwes97 , Check for non blank too

MEDIANX(SUMMARIZE('2021','2021'[SMI_CODE],"Comp1 AVG",
CALCULATE(AVERAGE('2021'[COMP3TGT]),'2021'[COMP3TGT]>0 && not(isblank([COMP3TGT])))),[Comp1 AVG])