Trouble Creating Average of Daily Averages - While Keeping it Filterable

[KStout]

I'm building an animal activity budget and need help calculating the average time animals spend engaging in different behaviors.

The approach we use is to calculate the daily percentage of time spent in each behavior, and then take the average of those daily percentages over time. This method helps smooth out outlier days.

Here’s what I need DAX to be able to calculate WHILE recalculating each time based on filters:

• For each day:
  Behavior Count for a category / Total Behavior Counts for that day
• I then need it to average of these daily percentages across all days.
• It’s essential to include days where the behavior wasn't observed at all. On those days, the behavior’s count should be treated as zero in the daily calculation — meaning the percentage is 0%, and that zero must be included in the final average.
• However, I also need to exclude days when no behaviors were observed at all (i.e., total behavior count for that day is 0), as those days aren’t valid for the average.
• The tricky part: all of this needs to update dynamically based on filters (like individual animal, location, or time of day). For example:
• On January 1, the porcupine was observed 5 times: 3 resting, 2 moving.
• But if I filter to only show "outside the den," only 3 of those 5 observations qualify — and only 1 of those 3 was resting.
• So, under the filter, the new daily percentage for resting becomes 1/3, not 3/5
• And if I filter to only show "inside den," 0 of the observations qualify — I need it to exclude this day now from the average of daily averages since the filter context makes the denominator 0.
• Finally, the end goal is to visualize these calculations as a dynamic column chart that updates automatically as filters change.

The issue I'm running into is that the calculations for daily averages often get hard-coded into summary tables, and then the measures no longer respond to filters. Also, I’ve struggled to create a measure that correctly includes zero-values for days when a behavior wasn’t observed — which inflates percentages (e.g., shuffling might show 30% instead of the real 8%). And finally, I think the days when the filter context makes denominator 0 might be giving some issue as well.

I’m concerned that maybe DAX doesn’t allow stepping back into the row context of a calculated table BEFORE the table is created.

In Excel, I would calculate these by generating tons of different pivot tables to analyze the various variables. However, using PowerBI in this way would save so much time if it were possible to get the average of daily averages. (I know % of total is easily calculated, however, I’m only looking for average of daily averages)

When I have sucessfully created a measure that links to the fact table, it does not include 0's for days of no obaservation for given activities and so the percentages are artificially inflated. ie) the porcupine spends 8% of his time shuffing. However, the measure says he spends 30% of his time shuffling. 

Here is the DAX I wrote to try and get around this issue. It unfortunately won't work either! DAX can't read just one step back to have the measure stay connected to the columns from which the table was calculated.

Can anyone help me with this issue? It has been breaking my brain. 

Summarize =

VAR Summary = ADDCOLUMNS(CROSSJOIN(VALUES('Fact Table'[Date]), VALUES('Fact Table'[Behavior Count])),

                         "CategoryCount4Day", COALESCE(CALCULATE(SUM('Fact Table'[Behavior Count]), 'Fact Table'[Date] = EARLIER('Fact Table'[Date])), 0),

                         "TotalCount4day", CALCULATE(SUM('Fact Table'[Behavior Count]), ALL('Fact Table'[Behavior Category])))

RETURN

ADDCOLUMNS(Summary, "average", [Count]/[Count4day])

Date	Hour	Behavior Category	Location	Interval Channel 3 Value	Interval Channel 5 Value	Question: How many guests are observed?_542
2025-04-27	11	Inactive				0
2025-04-28	9	Feed	Stump	Yes, I heard env. noises!		1-5
2025-04-28	10	Inactive				0
2025-04-28	13	Shuffle	Stump		Yes, I heard keeper noises!	1-5
2025-04-28	15	Locomotion	On rocks			0
2025-04-28	15	Inactive	On rocks			6-10
2025-04-29	8	Inactive	Stump	Yes, I heard env. noises!		0
2025-04-29	9	Shuffle	On rocks		Yes, I heard keeper noises!	0

[v-veshwara-msft]

Hi @KStout ,

Just checking in to see if you query is resolved and if any responses were helpful.
Otherwise, feel free to reach out for further assistance.

Thank you.

[v-veshwara-msft]

Hi @KStout ,

Thanks for posting in Microsoft Fabric Community and detailing the scenario.

I’ve created a sample PBIX using the data you shared and attempted to reproduce and address the issues you described - including dynamic filtering, handling of zeros, and exclusion of invalid days. Please take a look and let me know if this aligns with your expectations.

Measure used:

Average of Daily Percentages = 
VAR FilteredDates =
    CALCULATETABLE (
        VALUES ( 'Fact Table'[Date] ),
        ALLSELECTED ( 'Fact Table' )
    )

VAR FilteredCategories =
    CALCULATETABLE (
        VALUES ( 'Fact Table'[Behavior Category] ),
        ALLSELECTED ( 'Fact Table' )
    )

VAR DailyCategoryTable =
    ADDCOLUMNS (
        CROSSJOIN ( FilteredDates, FilteredCategories ),
        "BehaviorCount",
            CALCULATE (
                SUM ( 'Fact Table'[Behavior Count] ),
                'Fact Table'[Date] = EARLIER ( 'Fact Table'[Date] ),
                'Fact Table'[Behavior Category] = EARLIER ( 'Fact Table'[Behavior Category] )
            )
    )

VAR WithTotals =
    ADDCOLUMNS (
        DailyCategoryTable,
        "TotalForDay",
            CALCULATE (
                SUM ( 'Fact Table'[Behavior Count] ),
                FILTER (
                    ALLSELECTED ( 'Fact Table' ),
                    'Fact Table'[Date] = EARLIER ( 'Fact Table'[Date] )
                )
            )
    )

VAR WithProportions =
    ADDCOLUMNS (
        WithTotals,
        "ValidDay", IF ( [TotalForDay] > 0, 1, 0 ),
        "DailyPercentage", DIVIDE ( [BehaviorCount], [TotalForDay] )
    )

VAR FilteredDays =
    FILTER (
        WithProportions,
        [ValidDay] = 1
    )

RETURN
    AVERAGEX ( FilteredDays, [DailyPercentage] )

If you’re still seeing unexpected results, feel free to reach out again - and if possible, consider sharing a sample PBIX (with any sensitive data removed) so I can reproduce the setup more directly and assist further.

Thank you.

Please find the attached .pbix.

[KStout]

Unfortunately the same problem still persists. If you look at the totals, they add up to more than 100%.

Inactive 63%, Suffle 35%, Feed 20%, Locomotion 20%

This is because the program doesnt know how to calculate in 0's.

So I tweaked your formula to wrap the divide function in Coalaese( , 0). Based on the table I can view in DAX Query View, it seems like it would work! But it sadly does not. After coalesce, It gives me really wrong answers of 16%, 6%, 2%, 2%.

[v-veshwara-msft]

Hi @KStout ,

Thanks for the follow-up,

When using this measure

Average of Daily Percentages = 
VAR Dates =
    CALCULATETABLE (
        VALUES ( 'Fact Table'[Date] ),
        ALLSELECTED ( 'Fact Table' )
    )

VAR Categories =
    CALCULATETABLE (
        VALUES ( 'Fact Table'[Behavior Category] ),
        ALL ( 'Fact Table'[Behavior Category] ) 
    )

VAR DailyCategoryTable =
    ADDCOLUMNS (
        CROSSJOIN ( Dates, Categories ),
        "BehaviorCount",
            CALCULATE (
                SUM ( 'Fact Table'[Behavior Count] ),
                FILTER (
                    ALL ( 'Fact Table' ),
                    'Fact Table'[Date] = EARLIER ( 'Fact Table'[Date] ) &&
                    'Fact Table'[Behavior Category] = EARLIER ( 'Fact Table'[Behavior Category] )
                )
            )
    )

VAR WithTotals =
    ADDCOLUMNS (
        DailyCategoryTable,
        "TotalForDay",
            CALCULATE (
                SUM ( 'Fact Table'[Behavior Count] ),
                FILTER (
                    ALL ( 'Fact Table' ),
                    'Fact Table'[Date] = EARLIER ( 'Fact Table'[Date] )
                )
            )
    )

VAR WithProportions =
    ADDCOLUMNS (
        WithTotals,
        "ValidDay", IF ( [TotalForDay] > 0, 1, 0 ),
        "DailyPercentage", COALESCE( DIVIDE ( [BehaviorCount], [TotalForDay] ), 0 )
    )

VAR FilteredRows =
    FILTER (
        WithProportions,
        [ValidDay] = 1
    )

RETURN
    AVERAGEX ( FilteredRows, [DailyPercentage] )

the percentages correctly add up to 100 percent across categories for each day.

But when filtering to a single category like "Stump" on the 28th, it returns 0.20 instead of 0.50.

This might be happening because the internal table built using CROSSJOIN includes all behavior categories up front. Even if a filter is applied later, it doesn’t change the earlier steps where all categories were included using ALL or ALLSELECTED. So the filter doesn’t impact the calculation the way it's expected to.

This matches your observation that DAX doesn’t go back and change the earlier table based on slicer or visual filters applied later. The table becomes fixed once it is created inside the measure.

Hope this helps. Please reach out for further assistance.

Thank you.

[v-veshwara-msft]

Hi @KStout ,
Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.
Thank you.

[KStout]

Hi! Thanks for being so on top of replying. I'm sorry I've been taking so long to get back. 

Yes! That's exactly the problem I've been running into. Bascially, I'm trying to find a workaround that allows the table to be regenerated new each time based on the filter context, then runs the new calculation.

Do you think this would be impossible?

[v-veshwara-msft]

Hi @KStout ,
Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.
Thank you.

[Poojara_D12]

Hi @KStout 

You're working on a Power BI report for an animal activity budget and trying to calculate the average of daily behavior proportions—while ensuring that days with zero observations for a specific behavior are still counted in the average. This is essential for accuracy because excluding such zero-observation days would inflate the percentages, leading to misleading interpretations. For example, if the porcupine didn’t shuffle on certain days, those days still need to contribute a zero to the average so the final percentage reflects the true behavior frequency over time.

The main issue arises because most DAX aggregations like AVERAGEX don’t automatically include zero-value rows when the base data doesn't explicitly contain those combinations (e.g., a specific behavior not recorded on a day). To solve this, your approach of using CROSSJOIN between Date and Behavior Category is valid as it ensures every possible day-behavior combination exists—even if the count is zero. However, your current ADDColumns and CALCULATE expressions do not fully respect the filter context dynamically, especially when slicers are used (like filtering for behaviors outside the den), which is critical in your case.

The next step is to build a dynamic virtual table that:

1. Contains all combinations of dates and behavior categories (even zero entries).

2. Calculates the daily proportion of each behavior = behavior count / total behaviors on that day.

3. Averages those daily proportions across the filtered date range—even if some values are zero.

Here’s a DAX measure structure that could work dynamically:

Average of Daily Proportions =
VAR DaysAndBehaviors =
    ADDCOLUMNS (
        CROSSJOIN (
            VALUES ( 'Fact Table'[Date] ),
            VALUES ( 'Fact Table'[Behavior Category] )
        ),
        "BehaviorCount",
            CALCULATE (
                SUM ( 'Fact Table'[Behavior Count] )
            ),
        "TotalBehaviorForDay",
            CALCULATE (
                SUM ( 'Fact Table'[Behavior Count] ),
                ALLEXCEPT ( 'Fact Table', 'Fact Table'[Date] )
            )
    )
VAR WithProportions =
    ADDCOLUMNS (
        DaysAndBehaviors,
        "DailyProportion",
            DIVIDE ( [BehaviorCount], [TotalBehaviorForDay] )
    )
RETURN
    AVERAGEX ( WithProportions, [DailyProportion] )

Make sure your Date and Behavior Category columns are from dimension tables (not directly from the fact table) if possible. If they aren't, ensure relationships and filters behave correctly.

Place this measure on a column chart with axis = Behavior Category and values = this measure. It will recalculate dynamically when you apply filters, like only showing behaviors outside the den.

[KStout]

Thank you so much!

Unfortunately, this still doesn't work. It is unfilterable because it gets erroneous answers. When I do filter, I get averages that do not add up to 100%. I will attach some screenshots to show what happens. 

I need PowerBI to just start the filtering process one step earlier. I need it to filter BEFORE it creates the table each time. Becuase the filtering step needs to affect both the total number of behaviors for the day (denominator) and the number of behaviors per category (numerator) and then average those. It also needs to know that if the demoninator becomes 0, it should ignore those in the average of daily averages.  

I can have the dates pull from a dimension table, but the behaviors themselves are at the highest granularity of the data.

[KStout]

In DAX query view, I've also tried to see what the table looks like when filters are applied, and I can't even find a way for it to let me manually apply filters to the creation of the table. Which makes me think the creation of the table in that way is always going to be hard coded.

[maruthisp]

Hi KStout,

As per my understanding with your original problem description, I try to come up with a solution. Please find the attached pbix file. 
Creating Average of Daily Averages.pbix

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[KStout]

I appreciate you so much! Thank you for your help. 

Unfotunatlely the problem still exists. The pbix solution has percentages that add up to more than 100. This means that the code is still not accounting for 0's on days when observations were conducted but the behavior category was not observed. It also am suspicious of the averages if it were to reference a DIM calendar. Since there are some days on which there were no observations at all (the denominator would be 0).

But more importantly, i have been able to achieve correct calculations using group or summarize functions. However, once I do this step, it makes the answer unfilterable by any of the other columns. ie) filtering by location suddently gives percents over 100 - possibly meaning the total behaviors are being filtered, but the count of behavior categories are not. 

[FBergamaschi]

HI, please paste here a few rows of each table involved, so we can import in power bi, check the data, come back with questions and then solve

Best

FB