DAX. Filtering the calculation through a parameter.

[AleksandrG]

Guys, hello!

Please help me to solve the following problem.
I want to calculate the lost profit only for products whose profit is less than the specified value in the parameter.
I created a measure:

Lost profit =

SUMX (

    VALUES ( Assortment4 ),

    IF (

        [Remainder] = 0,

        VAR Profit =

            CALCULATE ( [Profit], ALLSELECTED ( 'Calendar' ) )

        VAR SalesQty =

            CALCULATE ( [Sales Qty], ALLSELECTED ( 'Calendar' ) )

        VAR SalesPerDay =

            CALCULATE ( [Sales Per Day], ALLSELECTED ( 'Calendar' ) )

        RETURN

            DIVIDE ( Profit, SalesQty, 0 ) * SalesPerDay

    )

)

She works as she should.

Further, I decided to improve this measure so that it calculates only for goods that have a profit greater than specified in the parameter.

Lost profit_2 =

VAR _1 =

    ADDCOLUMNS(  Assortment4, "Percent", [% Profit] )

RETURN

SUMX (

    FILTER (

        _1,

        [Percent] > '% Profit'[Meaning % Profit]),

    IF (

        [Remainder] = 0,

        VAR Profit =

            CALCULATE ( [Profit], ALLSELECTED ( 'Calendar' ) )

        VAR SalesQty =

            CALCULATE ( [Sales Qty], ALLSELECTED ( 'Calendar' ) )

        VAR SalesPerDay =

            CALCULATE ( [Sales Per Day], ALLSELECTED ( 'Calendar' ) )

        RETURN

            DIVIDE ( Profit, SalesQty, 0 ) * SalesPerDay

    )

)

But the Lost profit_2 measure doesn't work for some reason. I have changed the first argument to SUMX so that I can filter products by % profit.

Tell me, please, what is my mistake? How can this be fixed?

link to example: https://drive.google.com/file/d/1YUy1h8P0XBvXLvd3vsOHHjDEvhn9gMUR/view?usp=sharing

[amitchandak]

@AleksandrG , to filter a measure you need to force a group by. or use measure in the visual level filter

example

suxm(filter(values(Table[Name]), [percent] < selectedvalue(whatif[value]) ), [Measure])

[AleksandrG]

Hello)
Many thanks for the advice.
I'd really like to do this functionally - via Create Parameter if possible)
But is there really no way to make a filter through Create parameter - Numeric parameter?

It seems that everything is obvious, at first glance, how to implement it.)) But for some reason it does not work.
I have already started trying through creating a DAX virtual table.
Here's what happens:
If I create a virtual table without the FILTER function, then everything works as it should.

VAR _table =

    ADDCOLUMNS (

        Assortment4,

        "Persent", [% Profit],

        "Profit", CALCULATE ( [Profit], ALLSELECTED ( 'Calendar' ) ),

        "SalesQty", CALCULATE ( [Sales Qty], ALLSELECTED ( 'Calendar' ) ),

        "SalesPerDay", CALCULATE ( [Sales Per Day], ALLSELECTED ( 'Calendar' ) )

    )

RETURN

    SUMX (

        _table,

        IF ( [Remainder] = 0, DIVIDE ( [Profit], [SalesQty], 0 ) * [SalesPerDay] )

    )

But as soon as I start filtering the table through the created parameter, the data from the chart disappears.

VAR _table =

    FILTER (

        ADDCOLUMNS (

            Assortment4,

            "Persent", [% Profit],

            "Profit", CALCULATE ( [Profit], ALLSELECTED ( 'Calendar' ) ),

            "SalesQty", CALCULATE ( [Sales Qty], ALLSELECTED ( 'Calendar' ) ),

            "SalesPerDay", CALCULATE ( [Sales Per Day], ALLSELECTED ( 'Calendar' ) )

        ),

        [Persent] > '% Profit'[Meaning % Profit]

    )

RETURN

    SUMX (

        _table,

        IF (

            [Remainder] = 0,

            DIVIDE ( [Profit], [SalesQty], 0 ) * [SalesPerDay]

        )

    )

What am I doing wrong?) How to fix this?

[AleksandrG · Accepted solution]

here is the solution 🙂

VAR _table =

    FILTER (

        ADDCOLUMNS (

            Assortment4,

            "Persent", CALCULATE ( [% Profit], ALLSELECTED ( 'Calendar' ) ),

            "Profit", CALCULATE ( [Profit], ALLSELECTED ( 'Calendar' ) ),

            "SalesQty", CALCULATE ( [Sales Qty], ALLSELECTED ( 'Calendar' ) ),

            "SalesPerDay", CALCULATE ( [Sales Per Day], ALLSELECTED ( 'Calendar' ) )

        ),

        [Persent] > '% Profit'[Meaning % Profit]

    )

RETURN

    SUMX (

        _table,

        IF (

            [Remainder] = 0,

            DIVIDE ( [Profit], [SalesQty], 0 ) * [SalesPerDay]

        )

    )