topic Re: Changing number based on slicer and multiplying that new number by a value based on criteria in DAX Commands and Tips

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

Anonymous — Mon, 05 Aug 2024 12:22:59 GMT

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.

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

DataNinja777 — Fri, 09 Aug 2024 13:48:24 GMT

Hi Anonymous ,

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,

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

Anonymous — Fri, 09 Aug 2024 08:36:41 GMT

Hi Anonymous

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.