Skip to content

    • Charting

    Charting

    How to display bar totals correctly

    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!

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Answers

    • 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

      Are you on my newsletter? If not, signup here so you don't miss out on my Domo tricks, alerts about my webinars, cooking tips and more

      Signup here:

      https://www.dashboarddudes.com/newsletter

    • 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:

      1. case
      2. when ForecastCost < PlannedCost * 1.01 AND ForecastCost - PlannedCost > 20000 then 'Over Budget'
      3. when ForecastCost > PlannedCost * 1.01 AND ForecastCost - PlannedCost> 5000 then 'Over Budget'
      4. else 'Under Budget'
      5. 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!

    • 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

      Are you on my newsletter? If not, signup here so you don't miss out on my Domo tricks, alerts about my webinars, cooking tips and more

      Signup here:

      https://www.dashboarddudes.com/newsletter

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories