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

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

Anonymous's avatar
Anonymous
Not applicable
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,

     

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

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