cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anup_kumar
Frequent Visitor

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.

How can I solve this problem ?

 

Anup

 

2 REPLIES 2
TomMartens
Super User
Super User

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



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

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors