calculation based on the previous value
Hi all, I am stuck with creating a dax function, i really need help.. Here is an example, the final in the table should be my result, I have a value for 2019 which is the starting point of the calculation, then its about adding up the previous value calculated multiplied by the factor. Thanks in advance for your support.Calculate Average Price by row for aggregated Qty and Sales for various products.
I have a list of customer purchaces of various fuels for which I have summed the Qty and Sales by Category and Branch. I would like to find the average price of each product. I have attached a spreadsheet with Test Data and also including my desired output. Here is the DAX I used to sum the QTY. The Inv_Multiplier is a conversion factor for various fuels and can be ignored: Total gallons = CALCULATE(SUMX(History, History[QTY] * History[INV_MULTIPLIER])) Here is the DAX for Total Sales: Total Sales = CALCULATE(SUMX(History, History[Sales])) And here is the DAX for Avg Sell Price, which is driving me crazy. Avg Sell Price = DIVIDE(History[Total Sales],History[total Gallons],0) The problem is when I set my Avg Sell Price 'Summerization' to 'Sum', it sums up all the individual transaction's average prices, not good. If I set the Avg Sell Price 'Summerization' to 'Don't summarize', it gives me the Average Sell Price for each individual transaction, that's worse. What I hope to find is DAX that will allow me to calculate the the Avg Sell Price for each row. Desired output is below . Here is my Input History table: Here is a link to the spreadsheet. Let me know if this doesn't work as I have not tried this before. https://docs.google.com/spreadsheets/d/1F3dCbXLF1X2eu96kKRVmVtHDjKAwRDNZ/edit?usp=share_link&ouid=108795197017177326252&rtpof=true&sd=true Thanks, Bud
