Need help with this fromula

[Anonymous]

Hi,

I am trying to do something for a project and am having immense trouble. So I have an SQL file with over 7,000,000 records, and some of them contain weights, which are labeled as quantity. These same records with quantity also are classificied into 4 different categories. These four categories all cost different amounts every month, so I have a seperate excel file titled price entry with 48 rows, one seperate price for each month of each classification. I am trying to take the individual quantities and multiply them all by a selected  increase/decrease value, and then take those new quantity values and have that new quantity number multiply by one of the 48 price values in the table and then sum the new price into one easy measure. I currentl;y have been able to do it in columns but the problem is I am unable to change the quantity at all the way id like to in a table. Is there a way to make a measure that can do this?

Basically 

((Quantity Value) * (1 + % change)) * (one of 48 price values based on month and one of the 4 criteria values) 

and then have the final value be a sum of all the new prices with the applied % change and price for month and criteria.

Thanks!

[Anonymous]

Hi,

I am trying to do something for a project and am having immense trouble. So I have an SQL file with over 7,000,000 records, and some of them contain weights, which are labeled as quantity. These same records with quantity also are classificied into 4 different categories. These four categories all cost different amounts every month, so I have a seperate excel file titled price entry with 48 rows, one seperate price for each month of each classification. I am trying to take the individual quantities and multiply them all by a selected  increase/decrease value, and then take those new quantity values and have that new quantity number multiply by one of the 48 price values in the table and then sum the new price into one easy measure. I currentl;y have been able to do it in columns but the problem is I am unable to change the quantity at all the way id like to in a table. Is there a way to make a measure that can do this?

Basically 

((Quantity Value) * (1 + % change)) * (one of 48 price values based on month and one of the 4 criteria values) 

and then have the final value be a sum of all the new prices with the applied % change and price for month and criteria.

Thanks!

[Anonymous]

Hi @Anonymous ，

For the problem you are experiencing, the use of temporary tables can solve your needs. Temporary tables can be used to dynamically adjust quantities and calculate new price values without changing the original data.

Below is an example of my data that I hope will help you.

1.declaration of percentage change variables, e.g., 5 per cent increase

DECLARE @percentage_change DECIMAL(5, 2) = 0.05;

2.Creation of temporary tables to adjust quantities

CREATE TABLE #adjusted_records (
   id INT,
   adjusted_quantity DECIMAL(10, 2),
   category INT,
   month INT
);

 3.Insert the adjusted data into the temporary table

INSERT INTO #adjusted_records (id, adjusted_quantity, category, month)
SELECT
   id,
   quantity * (1 + @percentage_change),
   category,
   month
FROM
   records;

4.Use the temporary table to calculate the new values and summarize

WITH computed_values AS (
   SELECT
       ar.id,
       ar.adjusted_quantity,
       pe.price,
       (ar.adjusted_quantity * pe.price) AS new_value
   FROM
       #adjusted_records ar
   JOIN
       price_entry pe
   ON
       ar.month = pe.month AND ar.category = pe.category
)
SELECT
   SUM(new_value) AS total_sum
FROM
   computed_values;
-- Drop the temporary table
DROP TABLE #adjusted_records;

If you have any other questions, I've found the following document to help you out, and I hope it helps.

Temporal tables - SQL Server | Microsoft Learn

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[Anonymous]

Here are some pictures of what I am working with 

Basically looking to take quantity, multiply it by percent change and then take the new values and multiply them by the price values based on the "month" and "cut"

[Anonymous]

Hi @Anonymous ，

For your question, we tried to do it using two calculation columns to meet the requirement, the first calculation column to find the change in quantity and percentage, and then the second calculation column to find the new value and multiply them by the price value based on "month" and "cut", hope this helps you! I hope this helps, if there are any other questions, please contact me at the first time, I will continue to help you answer.

Adjusted Quantity = 'combined_data'[Quantity]*(1+'combined_data'[Percent Change]/100)

New value = 'combined_data'[Adjusted Quantity]*
CALCULATE(
    MAX('combined_data'[Price]),
FILTER('combined_data',
'combined_data'[Cut]=EARLIER('combined_data'[Cut])&&'combined_data'[Month]=EARLIER('combined_data'[Month])
)
)

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[Anonymous]

Hi, 

this is really good, but I have a few questions. You mention "we tried it using two calculation columns". Does that mean you created new measures or columns? Also, I would like the percent change to be something you can change with a slicer and the final value changes dynamcially as you change the slicer. Also, I do not want to put the percent changes in the SQL table.

Thanks!

[Anonymous]

Hi @Anonymous ,

I think your understanding is correct, I have added new columns, if you don't need the new calculated columns, this is the state I changed to metrics, two states to choose from, hope this helps.

Measure 1 = SUMX('combined_data','combined_data'[Quantity])*(1+SELECTEDVALUE('combined_data'[Percent Change])/100)

Measure = MAXX('combined_data','combined_data'[Measure 1])*
CALCULATE(
    MAX('combined_data'[Price]),
FILTER('combined_data',
'combined_data'[Cut]=SELECTEDVALUE('combined_data'[Cut])&&'combined_data'[Month]=SELECTEDVALUE('combined_data'[Month])
)
)

If you want to make changes based on Percent change, you can just put it in slicer, and this step doesn't need to be saved in SQL table, so you can use it without any problem.

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[Anonymous]

Hi,

I appreciate your response, but I do not think this will help me in the long run. I basically do not want to have the percent changes in my main SQL data table. I would prefer if it is its own table that I can put into a slicer and when I choose it, it multiplies by that number before then taking that new number and multiplying it by the associated price for cut and month.

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.

Thanks

[Anonymous]

Hi @Anonymous ,

I think I can now understand what you mean, we can create a percentage parameter in Power bi for dynamic selection.

Then create two slicers, one for cut and one for Date to do what you want.

If you still have questions, here is my example data, I hope it helps.

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[Anonymous]

Also, I noticed in both my file and yours, the measure 2 does not seem to be summing properly.

[Anonymous · Accepted solution]

Hi @Anonymous ,

For your card vision object that has no value inside and the value of measure2 is not calculated correctly, we can try to solve this by splitting the measure into two parts for use.

Measure 2 = 
CALCULATE(MAX('combined_data 1'[Price]),
FILTER('combined_data 1','combined_data 1'[Cut]=VALUES('combined_data 1'[Cut])&&'combined_data 1'[Month]=VALUES('combined_data 1'[Month])
)
)

Measure 3 = SUMX('combined_data 1','Parameter'[Measure]*[Measure 2])

For your second question, we can try using calculated columns and a new table to aggregate the data.

Column = SUMX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Quantity])

Table 2 = SUMMARIZE('Table','Table'[Date],"TotalQuantity",SUM('Table'[Quantity]))

For your new request I have updated the pbix file, I hope it helps you, it's my pleasure to help you.

Hope it helps!

Best regards,

Community Support Team_ Tom Shen

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

[Anonymous]

Hi,

Thank you so much, your solution was fantastic. I do have one more question though. So now that I successfully have that measure, I am looking to combine it with another measure that is all the stuff that was not a quantity. I am looking to sum up everything, and while I have tried a simple addition equation of my two measures. my visual keeps exceeding the alloted space. HJere are a few images. 

[Anonymous]

Also I tried a new formula and keep getting the exceeded error.

[Anonymous]

Hi,

This is really good, but I am still running into a few issues...

1.) I am running into an issue when I do not choose a cut or month and put it into a card. (Screenshot Below)

2. I have many days over the 6 year range that have more than one transaction, is there a way around this?

Again, thank you so much you've truly helped me exponentially.

[Anonymous]

[Anonymous]

Hi,

Is there a way to do this in Power Bi? I see this solution is in SQL server.