Forum Discussion
Elephant Data: Want to filter THEN transform the data and run calculations in report view
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.
| 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 |
6 Replies
v-echaithraCommunity Support
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.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?
- 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. - v-echaithra
Hi KStout ,
May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.Thank you.
