Dashboard reporting grouped information

Options
jacquedale
jacquedale ✭✭✭✭✭
edited 12/13/22 in Formulas and Functions

I am trying to show forecasted amounts by business unit. I was able to figure out how to create the report and group the sums by BU. However I only want to show the business units and the respective totals, on the dashboard. I can't for the life of me figure out how to pull only that info into the dashboard.


Jacque Smith

Project Controls, MSR-FSR

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you able to provide some screenshots for reference?

  • jacquedale
    jacquedale ✭✭✭✭✭
    Options

    Yes sorry, didn't thing to add screenshots. I have attached a screenshot of the source sheet. I need to show in a chart of some kind (on the dashboard). The business units and the total forecasted revenue for each business units for each year, but NA's need be omitted from the graphic. I also included the sheet summary for reference.





    Jacque Smith

    Project Controls, MSR-FSR

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @jacquedale

    I would create a metrics sheet with a row for each business unit, with columns for each forecast year and a totals column.

    Use SUMIFS to add up the values for each year for each BU, excluding anything equal to NA:

    =SUMIFS({Remote Sheet Forecasted Revenue '22 Column Range}, {Remote Business Unit Range}, [Business Unit]@row, {Remote Sheet Forecasted Revenue '22 Column Range}, ISNUMBER(@cell))

    In English - Give me the sum of values from the remote sheet's Forecasted Revenue '22 column, for all the rows where the Business Unit = the business unit from this row on the metrics sheet, and where the value in the Forecasted Revenue '22 column is a number value.

    Do this for each of the forecast columns, and then in the Total Forecasted Amount column:

    =SUM([Forecasted Revenue '22]@row:[Forecasted Revenue '24]@row)

    Then you can either create a report based on the metrics sheet, or use the metrics sheet as the basis for a graph or chart on the dashboard.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • jacquedale
    jacquedale ✭✭✭✭✭
    Options

    @Jeff Reisman


    Thank you so much! This worked perfectly!!! I just couldn't figure out the 2nd part of the formula (for the BU grouping). Thanks again!


    Jacque Smith

    Project Controls, MSR-FSR

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!