Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Need help with this fromula

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. Th...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

Hi,

 

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