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

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

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

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

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

        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