Hi gang! I've done a mockup here to try and explain what's going on with my dataset and what I want to achieve.
The first table below shows my dataset. We don't need the month column for this, but I wanted to include it to explain why each project has multiple rows in the dataset.
Essentially, I want to SUM the PlannedCost for each project and compare it to the SUM of the ForecastCost for each project. If the total Forecast is less than total Planned for each project, it's Under Budget, and the second and third tables below show this. I am displaying this as a Vertical bar chart, so in this case, it would show 2 projects Under Budget and 1 project over Budget.
I initially tried just SUM, then SUM(DISTINCT), then I've added in the FIXED piece. Still no success. My code is below. Vs my initial code, one issue now is I have no COUNT option on the Bar value within Analyzer.
case
when SUM(SUM(`ForecastCost`) FIXED (BY`ProjectName`)) < SUM(SUM(`PlannedCost`) FIXED (BY`ProjectName`)) then 'Under Budget'
else 'Over Budget'
End
Where am I going wrong? I'm not a coder or DOMO expert, so any help is greatly appreciated.