Dashboard reporting grouped information

jacquedale

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.

Paul Newcome

Are you able to provide some screenshots for reference?

jacquedale

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.

Jeff Reisman

@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.

jacquedale

@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!