Elephant Data: Want to filter THEN transform the data and run calculations in report view

[KStout]

Hi! I am creating animal sctivity budgets. To do this, I first need to calculate the % of each behavior observed each day and then take the daily average %. Using AverageX, I get incorrect numbers calculated becuase it does not know how to account 0 into the average for behavior/date combinations it doesnt see. 

Currently, I have been working around this by transforming my data in PowerQuery or with DAX, then using Average(Mean Daily % Observed). However, creating all possible combinations of date, individual, behavior, requires a lot of computation power and slows down my report. Plus it makes the visuals unable to be filtered by slicers on the page that are more granular than the date. ie) I cannot filter these tables by hour becuase in PowerBI, the program transforms, calculates daily %, then filters, then calculates mean daily %. I need it to filter, then transform, calculate daily %, then calculate mean daily %.  

How can I create something that can achieve the same calculations and be filterable with the hour time slider? Attached is a subset of the data on elephants :).

Tis DAX code doesn work becuase again, the table is generated BEFORE filters are applied.

Daily % Table Made with DAX =

VAR AllCombos =

    ADDCOLUMNS(

        CROSSJOIN(

            VALUES('Raw Data'[Date]), VALUES('Raw Data'[Focal Name]),

            FILTER(VALUES('Raw Data'[Behavior]), NOT(ISBLANK('Raw Data'[Behavior])) )),

        "DailyTotal",

            CALCULATE(

                SUM('Raw Data'[BCount]),

                ALLEXCEPT('Raw Data', 'Raw Data'[Date])

            ),

        "DailyBehaviorCount",

            COALESCE(CALCULATE(

                SUM('Raw Data'[BCount]),

                ALLEXCEPT('Raw Data', 'Raw Data'[Date]),

                'Raw Data'[Behavior] = EARLIER('Raw Data'[Behavior]) && 'Raw Data'[Focal Name] = EARLIER('Raw Data'[Focal Name])

            ), 0)

    )

VAR WithPercentages =

    ADDCOLUMNS(

        AllCombos,

        "DailyPct",

            IF([DailyTotal] = 0, BLANK(), DIVIDE([DailyBehaviorCount], [DailyTotal], 0)

    ))

RETURN

   WithPercentages

Focal Name	Date	Behavior	Location	Hour
Tonga	10/15/25	Rest	North	8
Tonga	10/15/25	Rest	North	8
Tonga	10/15/25	Eat	South	9
Tonga	10/15/25	Rest	North	9
Tonga	10/15/25	Walk	North	10
Tonga	10/16/25	Drink	South	8
Tonga	10/16/25	Rest	South	8
Tonga	10/16/25	Walk	South	8
Rafiki	10/16/25	Walk	South	8
Rafiki	10/16/25	Rest	South	9
Rafiki	10/16/25	Walk	North	9
Tonga	10/16/25	Walk	North	9
Tonga	10/16/25	Rest	South	10

[v-echaithra]

Hi @KStout ,

Just following up to see if the Response provided was helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Chaithra E.

[parry2k]

@KStout you should create a measure for average instead of a calculated table, and that measure will respect the filter/slicer selection.

[v-echaithra]

Hi @KStout ,

Thank you for reaching out to Microsoft Community.

Calculate the total count of all behaviors observed for a specific date and individual, respecting slicers and filters.

DailyTotal = CALCULATE(
SUM('Raw Data'[BCount]),
ALLEXCEPT('Raw Data', 'Raw Data'[Date], 'Raw Data'[Focal Name]))

Calculate the count of behaviors observed for each behavior and individual, again respecting slicers.

BehaviorCount = CALCULATE(
SUM('Raw Data'[BCount]),
ALLEXCEPT('Raw Data', 'Raw Data'[Date], 'Raw Data'[Focal Name], 'Raw Data'[Behavior]))

Calculate the Daily Behavior Percentage which is the key measure, it calculates the percentage of each behavior observed for each individual and day.

DailyPct = VAR DailyTotalValue = [DailyTotal]
VAR BehaviorCountValue = [BehaviorCount]
RETURN
IF(
DailyTotalValue = 0,
BLANK(),
DIVIDE(BehaviorCountValue, DailyTotalValue, 0))

Now calculate the mean daily percentage for all behaviors across different time slices example by hour:

AverageDailyPct = AVERAGEX(
VALUES('Raw Data'[Date]),
[DailyPct])

Hope this will help.
Thank you.

[KStout]

Hi, thank you, this is so close! Unfortunely, my numbers are a tad off from what they need to be. I doubled checked and ran the calculations in Excel and the Power Query Activity budget I created was correct. The one made with measures was just slightly off. Do you have any ideas why?