Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors