How to display bar totals correctly

PJG
PJG Member
in Charting

Sorry for the ambiguous title, but I'm not sure what words to use to explain this in a summarized way.

I have a dataset that captures financials for projects. Each project can have up to 9 financial categories, and financials are captured per month. Therefore, the dataset includes a row for every single category/month combination. Even a 3 month project can have up to 36 rows for financials if it utilized all 9 categories. The exact costs in the dataset are in a column for PlannedCost or ForecastCost

I have a card for a bar chart where I'm showing whether a project is under or over budget. This compares the PlannedCost to the ForecastCost via BeastMode. In total, there are 106 projects that meet my criteria at time of writing, so this is what the two bars should add up to, but the combined total of the Under Budget & Over Budget bar columns is 6406. This is because it's counting every single row from the dataset.

How can I get this chart to be based on the unique project count, i.e. on the total for all months/categories, rather than it counting every individual row?

Thanks!

Answers

  • DashboardDude
    DashboardDude Coach

    Hi @PJG

    Anyway you can share a screenshot of the entire analyzer for us to see? It will be easier to understand

    Circle.png

    John Le

    You're only one dashboard away.

    Click here for more video solutions: https://www.dashboarddudes.com/pantry

  • PJG
    PJG Member

    Hi @DashboardDude Sorry for taking a month+ to get back to you - been pulled in many different directions since. Is this useful at all?

    image.png

    My On-Budget code is:

    case
    
when ForecastCost < PlannedCost * 1.01 AND ForecastCost - PlannedCost > 20000 then 'Over Budget'
    
when ForecastCost > PlannedCost * 1.01 AND ForecastCost - PlannedCost> 5000 then 'Over Budget'
    
else 'Under Budget'
    
End

    Again, the problem seems to be that it's comparing every single Month/Cost Category combination row from the dataset when I just need it to compare the total per project. Maybe the question is simply how to I SUM all the PlannedCost and ForecastCost per project before performing the budget calculation?

    Thanks!

  • DashboardDude
    DashboardDude Coach

    Hi @PJG ,

    I think I understand but still a little vague. I think I would do a count distinct in the ETL that you can then use to do your sum or count.

    Circle.png

    John Le

    You're only one dashboard away.

    Click here for more video solutions: https://www.dashboarddudes.com/pantry

Categories