Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Measure or calculation with user input (slicer or filter)

dear PBI gurus   am struggling to find the easier way to create a calculation / measure with a dynamic input from user.   my starting table is as follows item item description FY Mo...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi Anonymous ,

    Sorry for the late reply, I modify my sample to contain more items.

    Then modify the formula like this:

    Ordered =
VAR _T =
    SUMMARIZE (
        'Table',
        'Table'[Item],
        "Ordered",
            MAXX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Item] = MAX ( 'Table'[Item] )
                        && 'Table'[dimension] = "Ordered"
                ),
                'Table'[Quantity]
            )
    )
RETURN
    SUMX ( _T, [Ordered] )

    Fcast M-* =
VAR _T =
    SUMMARIZE (
        'Table',
        'Table'[Item],
        "Fcast M-",
            MAXX (
                FILTER ( 'Table', 'Table'[dimension] = SELECTEDVALUE ( 'Table'[dimension] ) ),
                'Table'[Quantity]
            )
    )
RETURN
    SUMX ( _T, [Fcast M-] )

    Formula result =
VAR _T =
    SUMMARIZE (
        'Table',
        'Table'[Item],
        "Formula resut",
            ABS ( [Ordered] - [Fcast M-*] )
                / ( ABS ( [Ordered] ) + ABS ( [Fcast M-*] ) )
    )
RETURN
    SUMX ( _T, [Formula resut] )

    Get the correct result:

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
3 years ago

Hi Anonymous ,

Sorry I'm not very clear what you mean. When I turn on the total, it gets the result.

If your real sample is more complicated, would you like to expain more or give an example?

 

Best Regards,
Community Support Team _ kalyj

Anonymous's avatar
Anonymous
Not applicable
3 years ago

sorry, I was too quick in answering 🙂

so, an example of my dataset is as follows

 

the single rows are calculated correctly, however the total instead of being the sum of all entries shows only the max value (it seems), I would rather see the sum of all "Ordered" and "Forecast". Is it possible by adding a "SUM" operation in the formula?

 

thanks for your king feedback

 

Best regards

Moia79

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi Anonymous ,

    Sorry for the late reply, I modify my sample to contain more items.

    Then modify the formula like this:

    Ordered =
VAR _T =
    SUMMARIZE (
        'Table',
        'Table'[Item],
        "Ordered",
            MAXX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Item] = MAX ( 'Table'[Item] )
                        && 'Table'[dimension] = "Ordered"
                ),
                'Table'[Quantity]
            )
    )
RETURN
    SUMX ( _T, [Ordered] )

    Fcast M-* =
VAR _T =
    SUMMARIZE (
        'Table',
        'Table'[Item],
        "Fcast M-",
            MAXX (
                FILTER ( 'Table', 'Table'[dimension] = SELECTEDVALUE ( 'Table'[dimension] ) ),
                'Table'[Quantity]
            )
    )
RETURN
    SUMX ( _T, [Fcast M-] )

    Formula result =
VAR _T =
    SUMMARIZE (
        'Table',
        'Table'[Item],
        "Formula resut",
            ABS ( [Ordered] - [Fcast M-*] )
                / ( ABS ( [Ordered] ) + ABS ( [Fcast M-*] ) )
    )
RETURN
    SUMX ( _T, [Formula resut] )

    Get the correct result:

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      it does work 🙂

       

      thanks