The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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])
User | Count |
---|---|
11 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
24 | |
14 | |
13 | |
9 | |
8 |