Trying to create a DAX code in which I can only apply the formula on a filtered selection

[MarcoW91]

Hi all,

I got the following dax code:

Future Forecast =

    SUMX(

    'FC UnderRev',

    'FC UnderRev'[COS URev_FC] +

    'FC UnderRev'[COS URev_FC] * [WirePercentage] +

    'FC UnderRev'[COS URev_FC] * [WireRodPercentage] +

    'FC UnderRev'[COS URev_FC] * [ProfilePercentage] +

    'FC UnderRev'[COS URev_FC] * [PowderPercentage]

)

I want to apply this code only on the selected items from a filter which filters on DIM_SPGHierarchy[SPG].

If the item is not selected in the filter I want to apply the following formula:

  SUMX(

    'FC UnderRev',

    'FC UnderRev'[COS URev_FC])

This column DIM_SPGHierarchy[SPG] is also present in the endresult table and therefor is causing an issue as it will filter out the items which I put in the filter. I cannot get it to work with disconnecting filters to apply both formula's in the two cases (filtered out and filtered in).

Can someone please assist?

[Anonymous]

Hi @MarcoW91,

You can add a variable to store the selection items, then you can add if statements in your expression to achieve parameterized calculations:

Future Forecast =
VAR selection =
    VALUES ( DIM_SPGHierarchy[SPG] )
RETURN
    SUMX (
        'FC UnderRev',
        'FC UnderRev'[COS URev_FC]
            * (
                1
                    + IF ( "WirePercentage" IN selection, [WirePercentage] )
                    + IF ( "WireRodPercentage" IN selection, [WireRodPercentage] )
                    + IF ( "ProfilePercentage" IN selection, [ProfilePercentage] )
                    + IF ( "PowderPercentage" IN selection, [PowderPercentage] )
            )
    )

Regards,

Xiaoxin Sheng

[lbendlin]

I want to apply this code only on the selected items from a filter 

There's a DAX function for that - FILTERS().  Also read about VALUES() and ISFILTERED().