cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
moia79
Frequent Visitor

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
Month
dimension
Quantity
ABC
ABC-Description
2021
1
Ordered
55
ABC
ABC-Description
2021
1
forecast current month
60
ABC
ABC-Description
2021
1
forecast m-1
70
ABC
ABC-Description
2021
1
forecast m-2
70
ABC
ABC-Description
2021
1
forecast m-3
100

 

 

I need, for each item, to calculate a formula with "ordered" as fix input and variable selection of "corecast m-*". The complete formula is = ABS(Ordered - Forecast M-*)/(ABS(Ordered)+ABS(Forecast M-*)) and I would like to give possibility for each user to switch from "forecast m-1" to "forecast m-3" using a slicer or a filter.

 

Ending result would be a table like

 

ITEM    Item Descriptoion     Ordered      Fcast M-* (sselected by user)     Formula result

1 ACCEPTED SOLUTION

Hi @moia79 ,

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

vkalyjmsft_0-1666687306524.png

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:

vkalyjmsft_1-1666687419547.png

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.

View solution in original post

6 REPLIES 6
moia79
Frequent Visitor

thanks @v-kalyj-msft 

 

it works greatly. The only issue I've seen is that - since by table has multiple entry / groups - it has a strange behavior when I activate the total at the bottom of each column - meaning to calculate the same on aggregate level (imagine I need to aggregate the item per product family or per customer.

 

Is there anything I can do to allow also the total to display the sum of all the rows in a column correctly?

thanks

Hi @moia79 ,

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

vkalyjmsft_0-1665394916205.png

vkalyjmsft_1-1665394934700.png

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

 

Best Regards,
Community Support Team _ kalyj

sorry, I was too quick in answering 🙂

so, an example of my dataset is as follows

moia79_0-1665399310368.png

 

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

Hi @moia79 ,

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

vkalyjmsft_0-1666687306524.png

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:

vkalyjmsft_1-1666687419547.png

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.

it does work 🙂

 

thanks

v-kalyj-msft
Community Support
Community Support

Hi @moia79 ,

According to your description, here's my solution.

You can only use measures, because the value of calculated columns can't be affected by slicer. Create three measures.

Ordered =
MAXX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Item] = MAX ( 'Table'[Item] )
            && 'Table'[dimension] = "Ordered"
    ),
    'Table'[Quantity]
)
Fcast M-* =
MAXX (
    FILTER ( 'Table', 'Table'[dimension] = SELECTEDVALUE ( 'Table'[dimension] ) ),
    'Table'[Quantity]
)
Formula result =
ABS ( [Ordered] - [Fcast M-*] )
    / ( ABS ( [Ordered] ) + ABS ( [Fcast M-*] ) )

Get the result.

vkalyjmsft_0-1664939029833.png

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.