Summarize average monthly table

[nmckeown1]

I have built a summary table for total calls per month, per campaign. Struggling to get an average of the monthly total of all campaigns combined.

Campaigns_Monthly =

VAR MonthlyCounts =

SUMMARIZE(

    'call_table',

    'int dim_dates'[year_month],

    'call_table'[campaign_name],

    "Total Calls per month", CALCULATE(COUNT('call_table'[interaction_key]), 'int fact_helpline_chat_call_log'[target] in {"true"} && 'call_table'[campaign_name] in {"whatsapp", "calls", "chats"}))

    RETURN

 MonthlyCounts

This table is as expected and provides a summary of monthly calls per campaign.

 However, when calculating a measure for the Average, it's not providing an overall average of total calls (across all campaigns)

Monthly_average =

AVERAGEX('Campaigns_Monthly', 'Campaigns_Monthly'[Target Contacts per month])

E.G per month, 2000-3000 calls in total are answered. However the monthly average is calculating as 500. Some campaigns have very low monthly numbers, and others have higher. But I need the overall average for the total, whilst also being able to use the campaign slicer to change this.

[Anonymous]

Hi @nmckeown1 ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you

[Anonymous]

Hi @nmckeown1 ,

I wanted to check if you had the opportunity to review the information provided. If you still require support, please let us know, we are happy to assist you.

Thank you.

[Anonymous · Accepted solution]

Hi @nmckeown1 ,

Thanks for reaching out to the Microsoft fabric community forum. Also thank you @Praful_Potphode  for your response.

The issue occurs because the table you created with SUMMARIZE is a calculated table, which is static and doesn’t respond to slicers. That’s why the Campaign filter isn’t affecting your results.

To make the monthly average dynamic and reflect slicer selections, please use the following measure instead:

Overall Monthly Average =
VAR MonthlyTotals =
    SUMMARIZE(
        'call_table',
        'int dim_dates'[year_month],
        "TotalPerMonth",
            CALCULATE(
                COUNT('call_table'[interaction_key]),
                'int fact_helpline_chat_call_log'[target] IN {"true"} &&
                'call_table'[campaign_name] IN {"whatsapp", "calls", "chats"}
            )
    )
RETURN
    AVERAGEX(MonthlyTotals, [TotalPerMonth])

If you want the monthly average to show as a constant value across all months, use this version:

Static Monthly Average =
CALCULATE(
    [Overall Monthly Average],
    ALLSELECTED('int dim_dates'[year_month])
)

This will keep the same average across the table while still updating correctly when using the Campaign slicer.

Hope this helps. Please feel free to reach out for any further questions.

Thank you.

[Praful_Potphode]

Hi @nmckeown1 ,

are you using calculations to create table or measure.if you are creating  a new table using summarize and then try to filter using slicer.it wont work as there is no relationship between your new table and call table.

if possible share sample pbix file  to chcek wher it went wrong.

Thanks and Regards,

Praful

[nmckeown1]

Thanks for your reply.  I originally had the campaign name included as a column in the summarized table:

This is ideally what I need as this allows me to use the slicer.

However, the average measure does not calculate the monthly average:

 

For example, I need the monthly average to be static across a full year

E.G there are ~ 2000/3300 total target calls per month, so the monthly average for 2025 should be around ~2800 (e.g)

This will change to ~500 when selecting WhatsApp filter, for the monthly average across 2025.

[Praful_Potphode]

Hi @nmckeown1 

try below measure 

Overall_Monthly_Average =
VAR Monthly_Totals_Table =
    SUMMARIZE(
        'call_table',
        'int dim_dates'[year_month], -- Group by month
        "Monthly Total Calls", CALCULATE(
            COUNT('call_table'[interaction_key]),
            'int fact_helpline_chat_call_log'[target] IN {"true"} &&
            'call_table'[campaign_name] IN {"whatsapp", "calls", "chats"}
        )
    )
RETURN
    -- Average the "Monthly Total Calls" column from the virtual table
    AVERAGEX(
        Monthly_Totals_Table,
        [Monthly Total Calls]
    )

this would work with campaign slicer as well.

if the above measure doesn;t work, please provide sample output example so that it becomes clear.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[nmckeown1]

Thanks - I created the summarized table as above and did the average calculation as a seperate measure. The table does now give monthly counts: 

However the problem still exists.  The campaign slicer does not update anything in the visual:

The average still calculates the sum of monthly target calls.

Measures used:

Monthly Target = SUM('Monthly_totals_table '[Target Contacts per month])

Monthly_average = AVERAGEX('Monthly_totals_table', 'Monthly_totals_table'[Monthly Target])