MEDIANX Returning Incorrect Value with ALLSELECTED

[msmays5]

Hi all,

I've included all sample data and key DAX formulas here. My question is as follows:

I have a KPI [% Release Completed Points] which is [# of Release Completed Points] / [# of Release Loaded Points]. I am trying to calculate the MEDIAN for whichever Releases and Teams a user selects. No matter what I seem to try, I am not getting correct values. My latest attempts is as follows, but it is not working:

VAR _SelectedReleased = ALLSELECTED ( Releases[Release] )
VAR _SelectedTeams =
    FILTER (
        ALLSELECTED ( 'Delivery Team'[Delivery Team] ),
        'Delivery Team'[Delivery Team] <> "Team 7"
    )
VAR _AllRows =
    CROSSJOIN (
        _SelectedReleased,
        _SelectedTeams
    )
VAR _Result =
    MEDIANX (
        _AllRows,
        CALCULATE (
            [% Release Completed Points],
            _SelectedReleased,
            _SelectedTeams
        )
    )
RETURN
    _Result

Any guidance on where I am going wrong would be greatly appreciated.

Thanks in advance for any help you can provide.

[v-junyant-msft]

Hi @msmays5 ,

Have you solved your problem? If solved please mark the reply in this post which you think is helpful as a solution to help more others facing the same problem to find a solution quickly, thank you very much!

Best Regards,
Dino Tao

[123abc]

Here's a modified version of your DAX code:

VAR _SelectedReleased = ALLSELECTED ( Releases[Release] )
VAR _SelectedTeams =
VALUES (
'Delivery Team'[Delivery Team]
)
VAR _AllRows =
CROSSJOIN (
_SelectedReleased,
_SelectedTeams
)
VAR _Result =
MEDIANX (
_AllRows,
CALCULATE (
[% Release Completed Points],
_SelectedReleased,
_SelectedTeams
)
)
RETURN
_Result

Changes made:

1. Use VALUES() instead of FILTER(): Instead of using FILTER to exclude "Team 7", I've used VALUES to get all the selected teams. This will automatically exclude "Team 7" and give you a distinct list of selected teams.

2. Changed _SelectedTeams in the CALCULATE function: I changed _SelectedTeams to use VALUES() in the CALCULATE function. This ensures that the context transition works correctly when calculating [% Release Completed Points] for each row in _AllRows.

Give this a try and see if it produces the desired result. If you're still facing issues, please provide more details about the data model and the structure of your tables, and I'll do my best to assist you further.

[msmays5]

Thank you for your willingness to help! However, there are two issues I'm encountering:

1. My measure was misnamed -- it should be the Median across both the selected teams and the selected releases - so I believe I should be using ALLSELECTED(Deliver Team[Delivery Team]) 

2. When I implemented your code, I was still not getting the correct median, even when just looking across releases. I'm getting the same value for both the base measure and the median measure. Please see here for a visual of what I'm seeing. Could this have something to do with the base measure having a KEEPFILTERS in it?

Thanks again for your assistance

[123abc]

If you want to calculate the median across both selected teams and selected releases, you can use ALLSELECTED on both dimensions. Also, the issue you're facing might be related to the KEEPFILTERS in the base measure.

Let's modify the formula to address these concerns:

VAR _Result =
MEDIANX(
ALLSELECTED(Releases[Release], 'Delivery Team'[Delivery Team]),
[% Release Completed Points]
)

RETURN
_Result

This modification uses ALLSELECTED on both the Releases and Delivery Team dimensions within the MEDIANX function.

Regarding the issue with the base measure containing KEEPFILTERS, it depends on the context and the logic within the base measure. If the base measure uses KEEPFILTERS in a way that interferes with the calculation of the median, you might need to modify the base measure as well.

If the base measure is using KEEPFILTERS to enforce a specific context, you may need to adjust your base measure logic to work well with the overall calculation. Alternatively, you can provide more details about your base measure, and I can assist you in modifying it if necessary.

Please make sure to replace [% Release Completed Points] with the actual expression you are using for calculating the release completed points.

If you still encounter issues, please provide more details about the base measure, and I'll do my best to assist you further.

[msmays5]

Apologies for the delay in getting back to you, I've been out of pocket. Thank you for following up.

In your example, I recieve the error: "All column arguments of the ALL/ALLNOBLANKROW/ALLSELECTED/REMOVEFILTERS function must be from the same table."

Additionally, I've moved my base measure to the median calculation, hoping that would resolve the issue, but I'm still getting incorrect results:

VAR _SelectedReleased = ALLSELECTED ( Releases[Release] )
VAR _SelectedTeams = ALLSELECTED ( 'Delivery Team'[Delivery Team] )
VAR _AllRows =
    CROSSJOIN(
        _SelectedReleased,
        _SelectedTeams
    )
VAR _Result =
    MEDIANX (
        _AllRows,
        VAR _Nume = 
            CALCULATE ( 
                [# of Release Completed Points], 
                REMOVEFILTERS ( Releases ), 
                _SelectedReleased,
                REMOVEFILTERS ( 'Delivery Team' ),
                _SelectedTeams
            )
        VAR _Denom = 
            CALCULATE( 
                [# of Release Loaded Points], 
                REMOVEFILTERS ( Releases ), 
                _SelectedReleased,
                REMOVEFILTERS ( 'Delivery Team' ),
                _SelectedTeams
            )
        VAR _Result2 = DIVIDE ( _Nume, _Denom )
        RETURN
            _Result2
    )
RETURN
    _Result