GitHub user dosubot[bot] added a comment to the discussion: How to create a chart with advanced window functions in metrics?
<!-- Greeting --> Hi @alexdonskikh! I'm [Dosu](https://go.dosu.dev/dosubot) and Iām helping the superset team. <!-- Answer --> Superset supports advanced SQL metrics, including window functions, but the chart builder UI doesn't natively handle nested aggregations like "AVG of monthly SUM(revenue)" without custom SQL. To achieve your goal and keep filtering by item_category, you should create a virtual dataset (using SQL Lab or the dataset editor) with a query like: ```sql SELECT segment, month, SUM(revenue) AS monthly_revenue FROM your_table GROUP BY segment, month ``` Then, in a second step (either as a subquery or in a new virtual dataset), calculate the average monthly revenue per segment: ```sql SELECT segment, AVG(monthly_revenue) AS avg_monthly_revenue FROM ( SELECT segment, month, SUM(revenue) AS monthly_revenue FROM your_table WHERE item_category = '{{ filter_value }}' -- optional, for filtering GROUP BY segment, month ) t GROUP BY segment ``` You can define this as a virtual dataset, and then use it in your table chart. This approach lets you keep the raw structure for filtering and also add category-level metrics if needed. You can also define custom SQL metrics using window functions directly in the dataset editor or as ad hoc metrics in the chart builder, as long as your database supports them. For example, you could use something like `AVG(SUM(revenue)) OVER (PARTITION BY segment)` if your SQL dialect allows it [[source]](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/connectors/sqla/models.py). Be aware that table chart totals and summary rows may not always be correct for non-additive metrics like averages, since Superset computes totals independently of groupings. For complex aggregations, always test how filters interact with your metrics, and consider using virtual datasets for maximum flexibility [[source]](https://github.com/apache/superset/discussions/29297). If you want to show both segment-level and category-level metrics in the same table, you can add both metrics to your dataset or chart, but note that all metrics must be aggregates, and the same aggregation function applies to all fields in pivot tables [[source]](https://github.com/apache/superset/issues/34245). Let me know if you need a concrete SQL example tailored to your exact table or database! <!-- Dosu Comment Footer --> *To reply, just mention [@dosu](https://go.dosu.dev/dosubot).* --- <sup>How did I do? [Good](https://app.dosu.dev/response-feedback/64a77a20-01e2-49db-a1df-0c86404f5150?feedback_type=great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/64a77a20-01e2-49db-a1df-0c86404f5150?feedback_type=irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/64a77a20-01e2-49db-a1df-0c86404f5150?feedback_type=incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/64a77a20-01e2-49db-a1df-0c86404f5150?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/64a77a20-01e2-49db-a1df-0c86404f5150?feedback_type=hallucination) | [Report š](https://app.dosu.dev/response-feedback/64a77a20-01e2-49db-a1df-0c86404f5150?feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback/64a77a20-01e2-49db-a1df-0c86404f5150?feedback_type=other)</sup> [](https://app.dosu.dev/a28d3c7e-a9d3-459e-9fb6-3a6f9ff4f357/ask?utm_source=github)& nbsp;[](https://go.dosu.dev/discord-bot) [](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/apache/superset/discussions/34572) GitHub link: https://github.com/apache/superset/discussions/34572#discussioncomment-14014840 ---- This is an automatically sent email for notifications@superset.apache.org. To unsubscribe, please send an email to: notifications-unsubscr...@superset.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: notifications-unsubscr...@superset.apache.org For additional commands, e-mail: notifications-h...@superset.apache.org
