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.
How can I solve this problem ?
Anup
Hey @anup_kumar ,
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.
Regards,
Tom
Hi @TomMartens ,
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.
Measures defined as below
CY_Start_Date = if(datediff(min(Date_Table[Report_Date]),max(Date_Table[Report_Date]),year)=0,
FIRSTDATE(Date_Table[Report_Date]),
if(datediff(min(Date_Table[Report_Date]),max(Date_Table[Report_Date]),year)=1,
dateadd(FIRSTDATE(Date_Table[Report_Date]),1,year),
if(datediff(min(Date_Table[Report_Date]),max(Date_Table[Report_Date]),year)=2,
dateadd(FIRSTDATE(Date_Table[Report_Date]),2,year),
if(datediff(min(Date_Table[Report_Date]),max(Date_Table[Report_Date]),year)=3,
dateadd(FIRSTDATE(Date_Table[Report_Date]),3,year),
dateadd(FIRSTDATE(Date_Table[Report_Date]),4,year)))))
CY_End_Date = EDATE(MAX(Date_Table[Report_Date]),0)
PY_Start_Date = if(datediff(min(Date_Table[Report_Date]),max(Date_Table[Report_Date]),year)=0,
dateadd(FIRSTDATE(Date_Table[Report_Date]),-1,year),
if(datediff(min(Date_Table[Report_Date]),max(Date_Table[Report_Date]),year)=1,
FIRSTDATE(Date_Table[Report_Date]),
if(datediff(min(Date_Table[Report_Date]),max(Date_Table[Report_Date]),year)=2,
dateadd(FIRSTDATE(Date_Table[Report_Date]),1,year),
if(datediff(min(Date_Table[Report_Date]),max(Date_Table[Report_Date]),year)=3,
dateadd(FIRSTDATE(Date_Table[Report_Date]),2,year),
dateadd(FIRSTDATE(Date_Table[Report_Date]),3,year)))))
PY_End_Date = EDATE(MAX(Date_Table[Date]),-12)
CY_Revenue = CALCULATE(SUM(Sales[NET_SALES]),DATESBETWEEN(Date_Table[Date],Measures_AGGR[CY_Start_Date],Measures_AGGR[CY_End_Date]))
PY_Revenue = CALCULATE(Round( SUM(Sales[NET_SALES]) ,0),DATESBETWEEN(Date_Table[Date], Measures_AGGR[PY_Start_Date], Measures_AGGR[PY_End_Date]))
CY_Revenue_CL = CALCULATE(
SUMX(Sales, [CY_Revenue])
)
The SUMX measure does not work and gives a out of memory error .
Thanks
Anup
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!