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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sprakash1192
Helper II
Helper II

Total sum of running total

I have a measure that I use to do a running total using based on dates from my calendar table:

 
WCM_EU_Roll = CALCULATE(
SUM(WCM[Total_EUs_WCM]),
FILTER(
ALLSELECTED('Calendar'[Date]),
'Calendar'[Date]<= MAX('Calendar'[Date])
))
 
When I display this measure on the table, the row by row running total is correct, but the total at the bottom is not the sum total of all running totals, but last calculated running total (I am calculating year and week from the calendar table) :
 
 
 
YearWeekWCM_EU_Roll
2020292,972,714
2020303,077,064
Total 3,077,064

 

But, I would like the total to be the total sum of both the rows:

YearWeekWCM_EU_Roll
2020292,972,714
2020303,077,064
Total 6,049,778

 

I have tried HASONEVALUE to get around filters on the Total row, but it is not working.

 

Please help.

1 ACCEPTED SOLUTION

I had to group by multiple columns to get my roll forward numbers correct. So I created a new column using SUMMARIZECOLUMNS. Here is the DAX:

 

WCM_Summary = SUMMARIZECOLUMNS('Calendar'[Year],'Calendar'[Week],WCM[ProdFacName],WCM[Department_Code],WCM[Labor_Budget_Configuration (2).Hours],WCM[Labor_PerHR_Rate_Site_Dept.Hours],WCM[Labor_TargetEUs_Site_Dept_PerHR.Target_Eus_PerHR],"Total_WCM_EUs",WCM[WCM_EUs])

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@sprakash1192 , but is right. Running total at last point is the total you have in the system to date.

 

Try like

sumx(summarize(Table, Table[Week],"_1",[WCM_EU_Roll]),[_1])

 

or use summarizecolumnsin place of summarize

https://docs.microsoft.com/en-us/dax/summarizecolumns-function-dax

 

So I tried this:

WCM_EU_Total = sumx(SUMMARIZE('Calendar','Calendar'[Week],"Total_EUs",WCM[WCM_EUs]),[Total_EUs])  
 
But the totals are not correct. So I changed the formula to summarize by date because my running total measure is by date.
WCM_EU_Total = sumx(SUMMARIZE('Calendar','Calendar'[Date],"Total_EUs",WCM[WCM_EUs]),[Total_EUs])
 
But that is also not correct. Do I need to add more groups? Why wont it match with my running total?
 
You can see the difference:
YearWeekWCM_EU_TotalWCM_EUs
202029582516.682972714.99
202030417398.363077064.58

 

WCM_EU_Total is the summarized column. WCM_EUs is the running total column.

I had to group by multiple columns to get my roll forward numbers correct. So I created a new column using SUMMARIZECOLUMNS. Here is the DAX:

 

WCM_Summary = SUMMARIZECOLUMNS('Calendar'[Year],'Calendar'[Week],WCM[ProdFacName],WCM[Department_Code],WCM[Labor_Budget_Configuration (2).Hours],WCM[Labor_PerHR_Rate_Site_Dept.Hours],WCM[Labor_TargetEUs_Site_Dept_PerHR.Target_Eus_PerHR],"Total_WCM_EUs",WCM[WCM_EUs])

Thank you @amitchandak . Do I create a new meausre to use summarize?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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