Summing an average?

[wcloninger]

I have two tables. One shows date, gross sales, division name. The other has division name, event ID, date, and more details. I also have a date table linked to both of these tables. For one event,  you may have 3 divisions or you may have 174 associated with each event. I was thinking to have matrix look to this, but I want to show by event ID. My goal is to have a sum of daily gross sales per event and then get an average gross sales number of that day of the week. I would assume that filter would have an AllExcept function since it needs to be an average of the entire data model, not just for that event. This part is where I'm stuck. I can then take the difference to show trends.any assistance would be appreciated!

[DataNinja777 · Accepted solution]

Hi @wcloninger ,

First, create a measure that calculates daily gross sales per event by summing up the gross sales filtered to each event and day.

Daily Gross Sales per Event = 
SUM('SalesTable'[Gross Sales])

Ensure that this measure is used in a matrix visual, with Event ID and Date in the rows and columns respectively, so that each cell in the matrix represents daily gross sales for each event.

You’ll want to create a measure that calculates the average gross sales by the day of the week across all events. Assuming your date table has a column for the day of the week (e.g., DateTable[DayOfWeek]), you can create a measure that uses ALL or ALLEXCEPT to ignore the filters on Event ID but keep the filter on DayOfWeek.

Average Gross Sales by Day of Week = 
AVERAGEX(
    ALL('SalesTable'), 
    CALCULATE(
        SUM('SalesTable'[Gross Sales]), 
        ALLEXCEPT('DateTable', 'DateTable'[DayOfWeek])
    )
)

This measure calculates the average gross sales for a specific day of the week (e.g., all Mondays, all Tuesdays) across all events and dates, without being limited by a specific event.

Then, to see the trend or variance between the daily gross sales per event and the average for that day of the week, you can create a measure that calculates the difference.

Trend Difference = 
[Daily Gross Sales per Event] - [Average Gross Sales by Day of Week]

In your matrix visual:

• Place Event ID on rows, and Date on columns.
• Use the Daily Gross Sales per Event measure for gross sales per event.
• Add a new matrix or card visual to show Average Gross Sales by Day of Week and Trend Difference as desired, depending on whether you want it at the weekly level or broken out by day.

This setup should provide you with a clear view of daily gross sales per event, an average comparison for each day of the week, and a trend difference to show whether each event’s daily performance is above or below the weekly average for that day.

This solution leverages ALL and ALLEXCEPT to ensure you’re not constrained by event-specific filters while calculating averages, allowing you to get accurate comparisons for your trends.

Best regards,