Forum Discussion

nmckeown1's avatar
nmckeown1
Helper II
9 months ago
Solved

Summarize average monthly table

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 = SUMM...
Need Help
  • Anonymous's avatar
    Anonymous
    9 months ago

    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's avatar
Praful_Potphode
Super User
9 months ago

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's avatar
nmckeown1
Helper II
9 months ago

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])