Associate filters with sub-queries that fit into a dashboard

1

Hello,

I am a data analyst and I use the BI tool Metabase to analyse the data of my company.

At the moment, I am studying the cohorts of my entire company. For this purpose I have created several cohort tables that I have put together in a dashboard. This dashboard contains filters and as soon as I update a filter, my volume cohorts work and give me the expected results. However, I have also created a proportional cohort, i.e. base 100.
From a global point of view, at month = 0 I have a column filled with 100%. But as soon as I filter, my month = 0 column is no longer 100% because the filter I use in my dashboard does not affect the cohort_size data I use to get the proportion in a sub-query.

How can I get the sub-query that calculates the cohort size to update every time I filter in my dashboard?

Thank you for your feedback and thoughts that may shed some light on the situation.
Thank you very much,

EDIT : Please find attached the SQL query made in metabase to illustrate the above.

SELECT 
    date_trunc(`Foodcheri2_Fr_Bi_Cohortes`.`first_order_month`, month) AS `Foodcheri2_Fr_Bi_Cohortes__first_order_month`, 
    `Foodcheri2_Fr_Bi_Cohortes`.`anciente_month` AS `Foodcheri2_Fr_Bi_Cohortes__anciente_month`, 
    (CAST(count(distinct `Foodcheri2_Fr_Bi_Cohortes`.`customer_id`) AS float64) / CASE WHEN max(`source`.`count`) = 0 THEN NULL ELSE max(`source`.`count`) END) AS `cohortes_base_100`
FROM (
    SELECT 
        date_trunc(`bi_table_foodcheri2.foodcheri2_fr_bi_cohortes`.`first_order_month`, month) AS `first_order_month`, 
        count(distinct `bi_table_foodcheri2.foodcheri2_fr_bi_cohortes`.`customer_id`) AS `count` FROM `bi_table_foodcheri2.foodcheri2_fr_bi_cohortes`
WHERE 
    ( `bi_table_foodcheri2.foodcheri2_fr_bi_cohortes`.`anciente_month` = 0
    [[AND {{first_order_month}}]]
    [[AND {{Brand}}]]
    [[AND {{Clientele}}]]
    [[AND {{Lieux}}]]
    [[AND {{Hub}}]]
    [[AND {{Entreprise}}]]
    [[AND {{Service}}]]
    [[AND {{Category_coupon}}]]
    [[AND {{Code_coupon}}]]
    [[AND {{Description_coupon}}]]
    [[AND {{Campagne_name}}]]
    [[AND {{Campagne_description}}]]
    [[AND {{Canal}}]]
    [[AND {{Device}}]])
GROUP BY `first_order_month`
ORDER BY `first_order_month` ASC) `source`
LEFT JOIN `bi_table_foodcheri2.foodcheri2_fr_bi_cohortes` `Foodcheri2_Fr_Bi_Cohortes` ON date_trunc(`source`.`first_order_month`, month) = date_trunc(`Foodcheri2_Fr_Bi_Cohortes`.`first_order_month`, month) 
WHERE 
(1 = 1 
[[AND {{first_order_month}}]]
    [[AND {{Brand}}]]
    [[AND {{Clientele}}]]
    [[AND {{Lieux}}]]
    [[AND {{Hub}}]]
    [[AND {{Entreprise}}]]
    [[AND {{Service}}]]
    [[AND {{Category_coupon}}]]
    [[AND {{Code_coupon}}]]
    [[AND {{Description_coupon}}]]
    [[AND {{Campagne_name}}]]
    [[AND {{Campagne_description}}]]
    [[AND {{Canal}}]]
    [[AND {{Device}}]])
GROUP BY `Foodcheri2_Fr_Bi_Cohortes__first_order_month`, `Foodcheri2_Fr_Bi_Cohortes__anciente_month` 
ORDER BY `Foodcheri2_Fr_Bi_Cohortes__first_order_month` ASC, `Foodcheri2_Fr_Bi_Cohortes__anciente_month` ASC```