cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Wrong calculation of the medianX

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.

1 ACCEPTED SOLUTION
Community Support

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.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
3 REPLIES 3
Helper I

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...
Community Support

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.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User

@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])

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors