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,

 

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!