How to get an exact dashboard?

[aydar]

Hi everyone,

I’m trying to replicate a financial dashboard that was originally built in Power BI inside Metabase, and I’m running into several limitations, especially with the compact matrix-style table at the bottom.

The dashboard consists of a header section with multiple filters (YTD, PTD, Period, Analytic 1, Analytic 2), a gauge that shows actual versus plan with the maximum value equal to the total plan and displays budget utilization in percent, a line chart with two metrics (Plan amount and Actual amount) by month (displayed like M-8, M-7, etc., and ideally formatted in millions), and a compact matrix-style table that summarizes budget utilization by Analytic 1.

The most challenging part is the bottom table. It contains rows such as Investment flow, Taxation, Operating flow, Financial flow, and a Total row. The columns are Plan amount, Actual amount, Difference (Plan minus Actual), and Budget utilization percentage. In Power BI this is implemented as a Matrix visual with calculated columns and subtotals, and the layout is very compact and clean.

In Metabase I’m struggling with several things. First, creating calculated columns inside a grouped table (for example Difference and Budget utilization) in a clean and reusable way. Second, adding a proper total row that behaves consistently with the grouped rows. Third, making the table visually compact with reduced spacing between rows and columns. Fourth, formatting numeric columns consistently (currency, percentages, possibly millions display). And finally, achieving a matrix-like layout similar to what Power BI provides.

At the moment it seems that the only reliable way is to build the whole table using a native SQL query with pre-calculated fields and a UNION ALL for the Total row, but I’m wondering if there is a more “Metabase-native” approach using pivot tables, models, or saved questions.

What would be the recommended pattern in Metabase for building financial reporting dashboards like this? Is it best practice to push all calculations into SQL, or is there a cleaner modeling approach that works well for this type of reporting? Or should I dive into code?

Thanks in advance for any guidance.

[dwhitemv]

I don’t see why what you have there can’t be built in Metabase, but it’s going to be difficult duplicating Power BI look and feel. Power BI is first and foremost a layout engine; it’s focus is to produce standalone webpages with machinery to generate static datasets. Metabase trades detailed page layout capabilities for simple and quick dashboard assembly backed by real-time data delivery.

Have you tried a pivot visualization for the bottom table? Pivot viz and SQL can be tricky as the pivot needs to know what metrics it can summarize, but you can build a SQL saved question or model to feed a notebook question that has the necessary summarizations.

I’ll try to address your other points:

creating calculated columns inside a grouped table (for example Difference and Budget utilization) in a clean and reusable way

Metrics are pre-programmed aggregations that are applied to the query like other summary nodes. They are saved in the collection and are reusable.

adding a proper total row that behaves consistently with the grouped rows.

Again, feels like you want a pivot viz here, which will do the sums for you.

making the table visually compact with reduced spacing between rows and columns.

Metabase’s table layout doesn’t have size controls, so not much help there, sorry. Your screenshot doesn’t make the table look that compressed, though. The normal Metabase table display should fit fine.

formatting numeric columns consistently (currency, percentages, possibly millions display).

This can be done in the viz settings and in Table Metadata. “Compact” display types will resize values with metric prefixes.

@aydar

What would be the recommended pattern in Metabase for building financial reporting dashboards like this? Is it best practice to push all calculations into SQL, or is there a cleaner modeling approach that works well for this type of reporting? Or should I dive into code?

All depends on what you need and what you feel familiar working with. SQL gives you maximum flexibility at the cost of drill-through.

If you haven’t already, take a trip through the E-commerce Insights sample dashboard. Some of it is like what you are developing there. You can open up the questions and see how they’re set up. It might give you some ideas.

If you’re stuck on anything in particular, let us know and provide some sample data, and a mockup of what you want if it helps.