Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.