Forum Discussion

msmays5's avatar
msmays5
Icon for Helper II rankHelper II
2 years ago

MEDIANX Returning Incorrect Value with ALLSELECTED

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 ...
123abc's avatar
123abc
Icon for Community Champion rankCommunity Champion
2 years ago

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's avatar
msmays5
Icon for Helper II rankHelper II
2 years ago

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