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 II

## Changing number based on slicer and multiplying that new number by a value based on criteria

Hi,

I would like to ask for some assistance with this measure ZI am trying to complete. I am wanting to take a quantity value and multiply that number by a selected slicer value that ranges for -10% to 10%. I want to then take that and multiply it but a price value based on month of the year and one of four cuts. Every single cut has a different price by month, so there are basically 48 rows in the price tabl;e and I want the new quantity value that is affected by the slicer to multiply bny its associated price value.

I guess an example I would give is lets say there was a January 23rd transaction with a quantity of 40000 and the cut was Belly. I would want to be able to take that 40000 and on the slicer select a % increase or decrease. So lets say i choose -2%, I would want the new value to be 39,200. I then would want to take that 39,200 and multiply it by the price of January Belly. So lets say the January belly cost is 1.50, the ending transaction cost would be 58,800. I basically want to do that for every single transaction individually based on cut and month ideally in a singular measure.

Here are soime images of my main table, the percent change table and the price entry table.

1 ACCEPTED SOLUTION
Community Support

Thank you very much DataNinja777  for your prompt reply, please allow me to share some content here.

Here's some dummy data

"Main Table"

“Percent change”

“Price entry”

Create a relationship.

Create a measure.

``````Measure =
VAR _MONTH = SELECTEDVALUE('Price entry'[Month])
VAR _Price = SELECTEDVALUE('Price entry'[Price])
VAR _Percent = SELECTEDVALUE('Percent change'[Percent change]) * 0.01
VAR _TOTAL =
CALCULATE(
SUM('Main Table'[Quantity]),
FILTER(
ALL('Main Table'),
'Main Table'[Fiscal Month] = MAX('Main Table'[Fiscal Month])
&&
'Main Table'[Fiscal Month] = _MONTH
)
)

RETURN
IF(
_Percent < 0,
(_TOTAL + _TOTAL * _Percent) * _Price,
(_TOTAL - _TOTAL * _Percent) * _Price
)``````

Here is the result.

Regards,

Nono Chen

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

2 REPLIES 2
Community Support

Thank you very much DataNinja777  for your prompt reply, please allow me to share some content here.

Here's some dummy data

"Main Table"

“Percent change”

“Price entry”

Create a relationship.

Create a measure.

``````Measure =
VAR _MONTH = SELECTEDVALUE('Price entry'[Month])
VAR _Price = SELECTEDVALUE('Price entry'[Price])
VAR _Percent = SELECTEDVALUE('Percent change'[Percent change]) * 0.01
VAR _TOTAL =
CALCULATE(
SUM('Main Table'[Quantity]),
FILTER(
ALL('Main Table'),
'Main Table'[Fiscal Month] = MAX('Main Table'[Fiscal Month])
&&
'Main Table'[Fiscal Month] = _MONTH
)
)

RETURN
IF(
_Percent < 0,
(_TOTAL + _TOTAL * _Percent) * _Price,
(_TOTAL - _TOTAL * _Percent) * _Price
)``````

Here is the result.

Regards,

Nono Chen

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

Super User

The quantity in your screenshot shows all values as zero, but I assume this is just a sample. I also assume that your fact table with quantity information contains data about the four cuts in addition to date information. With this assumption, I would create a dimension table for the cuts and a calendar table. Then, I would apply the multiplication of quantity by price based on the month and cuts filtering.

Best regards,

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.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors