Hi @lpolanco85,

Good news: you can express this entirely inside the metric view. I reproduced your exact pattern (a percent-of-total whose PARTITION BY has one fewer column than the SELECT list) on a SQL warehouse and it works. The two pieces that unlock it are composable measures and a windowed expression that wraps MEASURE().

TWO RULES THAT MAKE THIS WORK

1. A measure's expr must be an aggregate, and to reference another measure you wrap it in MEASURE(). So your Efectividad measure should compose the two underlying measures rather than reference the raw columns directly:

measures:
  - name: ContadorReporte_Crm
    expr: SUM(ContadorReporte_Crm)

  - name: ContadorReporteResolucion_Crm
    expr: SUM(ContadorReporteResolucion_Crm) * 100

  - name: Efectividad_Crm
    expr: COALESCE(TRY_DIVIDE(MEASURE(ContadorReporteResolucion_Crm),
                              MEASURE(ContadorReporte_Crm)), 0)

2. For the percent-of-total columns, you can use SUM(MEASURE(x)) OVER (PARTITION BY ...) directly in the measure expr. The important detail I confirmed by testing: inside the OVER clause you reference the DIMENSION names, not the raw source columns. This is exactly what gives you the "PARTITION BY contains one fewer column than the SELECT" behavior you described, and you define it only once:

measures:
  - name: Pct_Distribucion_Abs_Crm
    expr: MEASURE(ContadorReporte_Crm) * 100.0
        / SUM(MEASURE(ContadorReporte_Crm)) OVER (PARTITION BY FechaReporte_Crm, ZonaVenta_CRM)

  - name: Pct_Distribucion_Efectividad_Crm
    expr: MEASURE(Efectividad_Crm) * 100.0
        / SUM(MEASURE(Efectividad_Crm)) OVER (PARTITION BY FechaReporte_Crm, ZonaVenta_CRM)

(FechaReporte_Crm and ZonaVenta_CRM here are the dimension names you defined in the dimensions block, not the underlying table columns.)

HOW IT BEHAVES WHEN YOU QUERY IT

With the measures defined once as above, the consumer query controls the level of detail and you never touch the metric view again:

SELECT
  FechaReporte_Crm,
  ZonaVenta_CRM,
  ClaseOperacionComercialId_Crm,
  MEASURE(ContadorReporte_Crm)        AS ContadorReporte_Crm,
  MEASURE(Efectividad_Crm)            AS Efectividad_CRM,
  MEASURE(Pct_Distribucion_Abs_Crm)   AS Pct_Distribucion_Abs_Crm,
  MEASURE(Pct_Distribucion_Efectividad_Crm) AS Pct_Distribucion_Efectividad_Crm
FROM datalakekof_dev.comercial_vista.com_vwm_crm_efectividad_mx
WHERE ZonaVenta_CRM IS NOT NULL
  AND FechaReporte_Crm = '20260505'
GROUP BY ALL
ORDER BY 2;

ClaseOperacionComercialId_Crm is in the SELECT, but the percent stays partitioned at the FechaReporte/ZonaVenta grain, so the distribution sums to 100 percent within each FechaReporte + ZonaVenta combination across all the Clase values. Add or remove a column in the SELECT and the partition stays fixed at the columns you named in the measure. In my test on sample data this produced exactly that result (percentages summing to 100 within each fixed partition).

If instead you want the denominator to follow whatever the query groups on (a true "percent of the visible total"), use an empty window and the denominator recomputes at the query grain automatically:

expr: MEASURE(ContadorReporte_Crm) * 100.0
    / SUM(MEASURE(ContadorReporte_Crm)) OVER ()

TWO THINGS TO KEEP IN MIND

- Reference dimension names (not raw source columns) inside PARTITION BY. Using the underlying table column there returns a grouping error, while the dimension name works.
- Every dimension you list in PARTITION BY needs to be present in the query's GROUP BY. If you partition by FechaReporte_Crm and ZonaVenta_CRM, those two must be in the SELECT / GROUP BY of the query. Filters in the WHERE clause are applied before the window, so the percentages are computed over the filtered, visible rows.

DOCS

Composability (defining measures in terms of other measures with MEASURE()):
https://docs.databricks.com/aws/en/metric-views/data-modeling/composability

Metric view YAML reference:
https://docs.databricks.com/aws/en/metric-views/yaml-ref

Querying metric views (MEASURE(), GROUP BY ALL):
https://docs.databricks.com/aws/en/business-semantics/metric-views/query

This keeps everything in one metric view, defined once, with the query free to add or drop dimensions without you having to maintain a separate measure per partition shape. Hope that helps, and feel free to share your final YAML if you would like another set of eyes on it.

* This reply used an agent system I built to research and draft this response based on the wide set of documentation I have available and previous memory. I personally review the draft for any obvious issues and for monitoring system reliability and update it when I detect any drift, but there is still a small chance that something is inaccurate, especially if you are experimenting with brand new features.

If this answer resolves your question, could you mark it as "Accept as Solution"? That helps other users quickly find the correct fix.