Applying a change to a value and then multiply by a certain value

[Anonymous]

Hi,

I am attempting to do something for a project and keep running into an issue. Basically I have 3 tables. One table is a range from -10 to 10 with .25 intervals that will be put into a slicer, the second is a SQL query with about 15 columns and the third is a price entry table that is adjusted in excel based on the market price of each cut. I am looking to take one of the columns in the SQL ( a number that represents quantity purchased) and multiply it by the selected slicer value. I then want to take this new amount and multiply it by an entered value in the price entry table based on the month of the year and the cut of meat that is it. Basically the price entry table is 48 rows, 12 for each month of the year for each of the 4 types of cut. Basically almost like I want to multiply the new value by an index match match but want it in a measure so that I can continue to adjust the slicer with the percent change. I have been able to do it in a calculated column, but the slicer adjustments cannot be done in calculated columns. I have also attached some images to better explain. 

I would appreciate if someone could assist me with it.

[Anonymous]

Thanks for the reply from gmsamborn.

Hi @Anonymous , 

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as solution to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards,
Zhu
Community Support Team

If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Anonymous]

yep

[Anonymous]

Hi @Anonymous ,

I'm sorry to bother you again, are you adjusting your data model based on gmsamborn’s reply, if so please mark his answer as solution. If you have any other solution, can you please share it? lt will help other users who have similar issues.

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[gmsamborn]

Hi @Anonymous 

I came up with a pbix (based on random data).

I created a simple date table and a slicer table and then used the following measures:

Price = AVERAGE( 'Prices'[Price] )

Qty = SUM( 'Sales'[Quantity] )

Total = [Qty] * [Price]

Rate = ( [Adj. Rate Value] / 100 ) + 1

Adj. Total = [Total] * [Price]

Let me know if you have any questions.

Jacobmiller23.pbix

[Anonymous]

I do not want the average I want the exact price associated with the month and cut. so if it is january trim, i want all the transactions that take place in january that are trim to be multipled by .99

[gmsamborn]

You didn't answer my question regarding is there ever a chance of 2 rows for the combination of month and cut?

Regarding your "exact" value, that's how Power BI and DAX work.  Choose the variation you would like if there is more than one row.

[Anonymous]

2 rows in the price entry table or the SQL?

[gmsamborn]

The price table.

I made the assumption (from your original post) that for every combination of month and cut there is only 1 row.  (unique)

Is that not the case?

[Anonymous]

Yes that is the case hence why I am confused wwhy it averaged every single price

[gmsamborn]

The only reason I used AVERAGE was for the case of 2 (or more) values for that combination of month and cut.  MAX or MIN could have been used.

In the case that you only have 1 value for that combination of month and cut, you would get the "exact" value regardless of if you use AVERAGE, MAX or MIN.

Does the case of multiple values ever come up?

[Anonymous]

Thisa happened

[gmsamborn · Accepted solution]

Have you checked the relationships in my model and compared them with your own?  

[Anonymous]

When I try and do the average function it does not work the way I want it to

[gmsamborn]

Please show me what you mean by "does not work the way I want it to".

[Anonymous]