Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
I have a data model that has 2 basic tables (Marging_Turnover table & Master_Calendar table). I have attached a PWBI file for reference.
Explanation
Margin_turnover table has only 3 columns, one is Yearmonth_num and the other 2 columns are:
- Margin
- Turnover
Expected_result using a Calculated column:
I have created a Calculated Column to show exactly the Result I am looking called "Rolling_sum", that is calculated from column Profit_column (Profit_column basically multiplies Margin*Turnover)
Rolling_sum =
CALCULATE(sum(Marging_Turnover[Profit_column]),
FILTER(ALL('Master Calendar'[Year_month_num]),'Master Calendar'[Year_month_num]<=max('Master Calendar'[Year_month_num])))
What I need to achieve?
Create a Measure without using Profit_colum or any other that is the product from Marging*Turnover in a row-wise style. I am pretty sure that the way to do it might be using SUMX as it is supposed to be a row-wise function, but I am not able to get it.
NOTE: NOT Calculated Column are allowed, and you cannot use Profit_column, it is only as reference.
My attempt is called "Rolling sum with SUMX" in Orange Color on the image Below
sumx(VALUES('Master Calendar'[Year_month_num]),
CALCULATE(
sum(Marging_Turnover[Marging])*sum(Marging_Turnover[Turnover]),
filter(all('Master Calendar'[Year_month_num]),'Master Calendar'[Year_month_num]<=max('Master Calendar'[Year_month_num]))))
I have attached a simple PWBI file
Solved! Go to Solution.
Rolling_sum_with_sumx =
var m = max('Master Calendar'[Year_month_num])
var a = SUMMARIZE(ALLSELECTED('Master Calendar'),[Year_month_num],"sm",sum(Marging_Turnover[Profit_column]))
return SUMX(FILTER(a,[Year_month_num]<=m),[sm])
Rolling_sum_with_sumx =
var m = max('Master Calendar'[Year_month_num])
var a = SUMMARIZE(ALLSELECTED('Master Calendar'),[Year_month_num],"sm",sum(Marging_Turnover[Profit_column]))
return SUMX(FILTER(a,[Year_month_num]<=m),[sm])