Forum Discussion
Total Calculation based on Total/Day for given Month
- 4 years ago
troyhimes
I Plead guilty 🙂
You are absolutely right. I overlooked the data and did not pay enough attention. I did not notice that the Tons/Day values are only in the first day while in other days are blank which is actually your original problem.
Please refer to updated file with the solution https://www.dropbox.com/t/RYcaBXfL2RcdQd5S
The code is little long. I know it can be shorter and more simple by using GENERATE function but I personally still did not learn how to use it properly. Also can be solved by adding a new calculated column. Pretty sure other methods are available out there. However this one seems to workMMP_Tons = VAR Table1 = SUMMARIZE ( Actuals_and_MonthlyPlans, Actuals_and_MonthlyPlans[Area], 'Calendar'[YYMM], "@Tons", SUM ( Actuals_and_MonthlyPlans[MMP_Tons/Day]) ) VAR Table2 = SELECTCOLUMNS ( Table1, "@YYMM", 'Calendar'[YYMM], "@@Tons", [@Tons] ) VAR Table3 = CROSSJOIN ( 'Calendar', Table2 ) VAR Table4 = FILTER ( Table3, [YYMM] = [@YYMM] ) VAR Result = SUMX ( Table4, [@@Tons] ) RETURN ResultPlease let me know if this solves your problem. Have a nice day!
troyhimes
I Plead guilty 🙂
You are absolutely right. I overlooked the data and did not pay enough attention. I did not notice that the Tons/Day values are only in the first day while in other days are blank which is actually your original problem.
Please refer to updated file with the solution https://www.dropbox.com/t/RYcaBXfL2RcdQd5S
The code is little long. I know it can be shorter and more simple by using GENERATE function but I personally still did not learn how to use it properly. Also can be solved by adding a new calculated column. Pretty sure other methods are available out there. However this one seems to work
MMP_Tons =
VAR Table1 =
SUMMARIZE (
Actuals_and_MonthlyPlans,
Actuals_and_MonthlyPlans[Area],
'Calendar'[YYMM],
"@Tons", SUM ( Actuals_and_MonthlyPlans[MMP_Tons/Day])
)
VAR Table2 =
SELECTCOLUMNS (
Table1,
"@YYMM", 'Calendar'[YYMM],
"@@Tons", [@Tons]
)
VAR Table3 =
CROSSJOIN ( 'Calendar', Table2 )
VAR Table4 =
FILTER ( Table3, [YYMM] = [@YYMM] )
VAR Result =
SUMX ( Table4, [@@Tons] )
RETURN
Result
Please let me know if this solves your problem. Have a nice day!