cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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
Community Support

Hi @moia79 ,

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.

6 REPLIES 6
Helper I

thanks @v-yanjiang-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

Community Support

Hi @moia79 ,

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

Helper I

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?

Best regards

Moia79

Community Support

Hi @moia79 ,

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.

Helper I

it does work 🙂

thanks

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.

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors
Top Kudoed Authors