Store measure output in a table for year over year analysis
I have a sales table with revenue, qty by product and time. I want to do an price-volume analysis by performing for each material the formula below
Current Year ASP = sum(Current Year Revenue)/ sum(Current Year quantity)
Prior Year ASP = sum(Prior Year Revenue)/ sum(Prior Year quantity)
Price Impact = (Current Year ASP - Prior Year ASP ) * Prior Year Quantity.
As of now, the Current/Prior Year Revenue and Quantity are defined as Measures and they work fine.
ASP cannot be calculated as a measure as it will divide the sum of revenue of different type of products by sum of their quantity. This will be incorrect. So I need to calculate ASP at each row level and then based on that ASP calculate the ASP change.
I tried using summarize columns to convert the cy, py number matrix in a table so that i can then do row by row calculation. But all combinations of summarize, summarize,sumx columns are failing either not giving right results or giving error Memory allocation failure.
without detailed knowledge about your data model, it's difficult to provide profound advice.
Nevertheless, I assume you can utilize the table iterator function SUMX (https://dax.guide/sumx/) that allows iterating across a table, maybe the fact table that contains the quantity and revenue columns to perform your calculations on a row level.
Did I answer your question? Mark my post as a solution, this will help others!
Proud to be a Super User! I accept Kudos 😉 Hamburg, Germany
Data model looks something like below Sales table - Material, InvoiceDate, Country, Entity, Qty, Net Revenue Date Table - standard date table with key as date Entity Master - Entity, Entity Name Material Master - Material, Material Name, Other Attributes Geography Master - Country, Country name.