topic Store measure output in a table for year over year analysis in DAX Commands and Tips

Store measure output in a table for year over year analysis

anup_kumar — Thu, 12 Mar 2020 07:17:32 GMT

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

Re: Store measure output in a table for year over year analysis

TomMartens — Thu, 12 Mar 2020 08:43:43 GMT

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

Re: Store measure output in a table for year over year analysis

anup_kumar — Thu, 12 Mar 2020 21:42:10 GMT

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