Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a measure that I use to do a running total using based on dates from my calendar table:
Year | Week | WCM_EU_Roll |
2020 | 29 | 2,972,714 |
2020 | 30 | 3,077,064 |
Total | 3,077,064 |
But, I would like the total to be the total sum of both the rows:
Year | Week | WCM_EU_Roll |
2020 | 29 | 2,972,714 |
2020 | 30 | 3,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.
Solved! Go to 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])
@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:
Year | Week | WCM_EU_Total | WCM_EUs |
2020 | 29 | 582516.68 | 2972714.99 |
2020 | 30 | 417398.36 | 3077064.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])
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |