Interactive dashboard for the data

Hi @Abdul Razack,

To better understand the issue, I have initiated a private message session to ask for more details since the information includes your personal information. When you have a moment, please click on “View message” at the top of this thread to open the private conversation and review the document.

Thank you for your patience and understanding throughout this process. I'm looking forward to your response.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

Use Excel PivotTables, PivotCharts, Slicers, and Timelines to build the interactive dashboard and support MoM/QoQ/YoY and region/function views from a large dataset.

1. Prepare the data

• Store all 100,000+ rows in a single Excel table (Insert > Table).
• Ensure fields include at least: GL, Value, Date, Region, Function (and any other needed dimensions).

2. Create a base PivotTable
3. Select any cell in the data table.
4. Go to Insert > PivotTable and place the PivotTable on a new worksheet.
5. In the PivotTable Fields pane:
   • Put GL in Rows.
   • Put Value in Values (set to Sum if needed).
   • Add Region and Function as additional fields (for later filtering/slicers).
   • Add Date as a field (you can group it by Month, Quarter, Year in the PivotTable).
6. Build separate PivotTables for each comparison type Create multiple PivotTables from the same data source (copy the first PivotTable and adjust fields):

• Month-on-Month (MoM)
  • Rows: GL
  • Columns: Month (group Date by Months and Years).
  • Values: Sum of Value.
  • Add a calculated field or separate measure (if using Data Model/Power Pivot) to compute the difference between current month and previous month. This difference will be used for the bar chart.
• Quarter-on-Quarter (QoQ)
  • Rows: GL
  • Columns: Quarter (group Date by Quarters and Years).
  • Values: Sum of Value.
  • Use a similar calculation to show difference vs previous quarter.
• Year-on-Year (YoY)
  • Rows: GL
  • Columns: Year (group Date by Years).
  • Values: Sum of Value.
  • Add a calculation for difference vs same period previous year.
• Region-wise MoM/QoQ/YoY
  • For each of the above PivotTables, add Region to Filters (or Rows/Columns depending on layout).
  • Alternatively, create separate PivotTables where Region is in Columns and GL in Rows, still using Month/Quarter/Year groupings.
• Function-wise QoQ
  • Rows: GL
  • Columns: Quarter.
  • Filters or Columns: Function (or separate charts per Function).
  • Values: Sum of Value and difference vs previous quarter.

4. Create PivotCharts with GL on X-axis, Values on Y-axis, and Difference as bar For each PivotTable:
5. Click inside the PivotTable.
6. Go to PivotTable Analyze > PivotChart.
7. Choose a combo chart or clustered column chart:
   • Series 1: Sum of Value (e.g., line or column).
   • Series 2: Difference (MoM/QoQ/YoY) as a bar/column.
8. Use the PivotChart Analyze, Design, and Format tabs to:
   • Ensure GL is on the X-axis.
   • Values and Difference are on the Y-axis (Difference as bar graph).
   • Format colors and styles as needed.
9. Rename each PivotChart via PivotChart Analyze > PivotChart > Chart Name so they’re easy to identify.
10. Add Slicers for interactive filtering
11. Select any PivotTable.
12. Go to PivotTable Analyze > Filter > Insert Slicer.
13. Insert slicers for:
    • Date (or separate Year/Month fields if created).
    • Region.
    • Function.
14. Arrange and resize slicers on the dashboard sheet.
15. To connect slicers to all related PivotTables:
    • Select a slicer.
    • Go to Slicer > Slicer > Report Connections.
    • Check all PivotTables that should respond to that slicer (e.g., all MoM/QoQ/YoY and region/function views).
16. Add a Timeline for date-based flux selection
17. Select any PivotTable.
18. Go to PivotTable Analyze > Filter > Insert Timeline.
19. Choose the Date field.
20. Resize and place the Timeline on the dashboard.
21. Connect it to all PivotTables via Options (Timeline) > Report Connections.
22. Use the Timeline to select specific months/quarters/years; the PivotTables and charts will update, enabling:
    • MoM: select a single month; calculations compare to previous month.
    • QoQ: select a quarter; calculations compare to previous quarter.
    • YoY: select a month or year; calculations compare to same period prior year.
23. Arrange the dashboard

• Place all PivotCharts on a single worksheet.
• Group charts logically:
  • MoM (overall, region-wise, function-wise).
  • QoQ (overall, region-wise, function-wise).
  • YoY (overall, region-wise).
• Align slicers and Timeline for a clean layout.

8. Share the dashboard

• Save the workbook to SharePoint or OneDrive.
• Share with stakeholders; they can interact with slicers and Timeline to view GL vs Value and Difference across MoM/QoQ/YoY, Region, and Function.

References:

• Create and share a Dashboard with Excel and Microsoft Groups
• Create an Excel Services dashboard using SQL Server Analysis Services data
• Create an Excel Services dashboard using a Data Model (SharePoint Server 2013)