I have a complicated issue due to how the shareholders want the data displayed and the calculations involved.
What I am trying to do is simply get for Month 1 for the [Projected Carry From Prior Month] to be
Month 0 ([Shipments Calc] + Month 0 [LOD Actual Demand]) - Month 0 [Forecast) resulting in 24,422 ((14,490 + 111,872)-101,940).
I kept the Old Formula for the [Projected Carry From Prior Month] metric in the workbook as it is made up of three calculations. The issue is when i bring in [LOD Actual Demand] I get the error of cannot mix aggregate and non-aggregate. No matter what I try I cant get the error to fix unless I break the [LOD Actual Demand] by taking off the additional SUM in the formula which results in the wrong answer. Any help would be greatly appreciated. Tried multiple
Expected End Result of formula:
Month 0 is 0 by default since there is no carryover. So for
Month 0 the calc should show 0,
Month 1 the calc should show 24,422
Month 2 the calc should show 12,844
Month 3 the calc should show 13,756
resources before asking here and they could not figure this out
(
Thank you. I actually solved my own issue by (like most issues in Tableau) restructuring my data tables using SQL since Tableau can't handle calculations like PowerBI or Microstrategy. I simply combined the Actual Demand with the Past Due Tons before ingesting it to Tableau, therefore eliminating the additional LOD for Past Due Tons. Its wild to me how limiting Tableau is with calculations especially if you have used other BI tools. Thank you all for your help.
