Forum Discussion

AleksandrG's avatar
AleksandrG
Helper I
3 years ago
Solved

DAX. Filtering the calculation through a parameter.

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?

Need Help
Show and Tell
  • AleksandrG's avatar
    AleksandrG
    3 years ago

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

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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's avatar
      AleksandrG
      Helper I
      3 years ago

      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's avatar
        AleksandrG
        Helper I
        3 years ago

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