topic Re: Help with calculating an average daily % of lion behavior in DAX Commands and Tips

Help with calculating an average daily % of lion behavior

KStout — Mon, 22 Dec 2025 20:27:34 GMT

Help please :), I have been at this for ages and cannot get the correct answer with DAX without first creating a static table.

I am after a mean daily average that must account for days with 0 values. Please see the code in the picture below or refer to the botom of the text box.

If I use [Date] as a small multiple and I look at the calculations day by day, they match for both the static table and the DAX measure which uses a virtual table. However, the values differ from the verified answers when I take out the date as a small multiple. Can someone help me to figure out what is going wrong?

[BCount] is simply a colum that contains 1's - so that i can just use sum functions instead of row counts. I need (the total behaviors per category per lion each day) / (the total number of behavior including all categories, per lion, per day) and I need that to coalease to a 0 value if the behavior category was not observed at all that day - hence the necessity for a crossjoin. Then I need it to average all those values out to get mean daily % of each behavior observed.

Here is a screenshot of the original data format - 'Keeper Data'

And here is a screenshot of the static table I created with PowerQuery to get the correst answers - I can also create the same static table with DAX. But I need the table to be filterable and dynamic. It cannot be static becuase I need to be able to filter by values more granular than the date.

DAX Test1 Average % Each Day =

VAR dailys = COALESCE(CALCULATE(SUM('Keeper Data'[BCOUNT])+0)/CALCULATE(SUM('Keeper Data'[BCOUNT]), ALL('Keeper Data'[Behavior])), 0)

return

AVERAGEX(

CROSSJOIN(

SUMMARIZE('Keeper Data',

'Keeper Data'[Date],'Keeper Data'[Individual]

VALUES('Keeper Data'[Behavior])

)

, (dailys + 0))

Re: Help with calculating an average daily % of lion behavior

lbendlin — Mon, 22 Dec 2025 21:32:55 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Help with calculating an average daily % of lion behavior

tamerj1 — Tue, 23 Dec 2025 02:59:12 GMT

Hi KStout

You need to push the CALCULATE expression to the inside of the AVERAGEX iteration in ordert to force context transition

DAX Test1 Average % Each Day =
AVERAGEX (
CROSSJOIN (
SUMMARIZE ( 'Keeper Data', 'Keeper Data'[Date], 'Keeper Data'[Individual] ),
VALUES ( 'Keeper Data'[Behavior] )
),
DIVIDE (
CALCULATE ( SUM ( 'Keeper Data'[BCOUNT] ) ) + 0,
CALCULATE ( SUM ( 'Keeper Data'[BCOUNT] ), ALL ( 'Keeper Data'[Behavior] ) )
)
)

Re: Help with calculating an average daily % of lion behavior

KStout — Tue, 23 Dec 2025 16:08:45 GMT

this still unforntunately leaves days unaccounted for. Maybe I will try it again in another project becuase nothing seems to work.

Re: Help with calculating an average daily % of lion behavior

Praful_Potphode — Wed, 24 Dec 2025 09:09:53 GMT

Hi KStout ,
try below measure:

DAX Test1 Average % Each Day = AVERAGEX( CROSSJOIN( SUMMARIZE('Keeper Data', 'Keeper Data'[Date], 'Keeper Data'[Individual] ), VALUES('Keeper Data'[Behavior]) ), VAR _Date = 'Keeper Data'[Date] VAR _Individual = 'Keeper Data'[Individual] VAR _Behavior = 'Keeper Data'[Behavior] RETURN DIVIDE( CALCULATE( SUM('Keeper Data'[BCOUNT]), 'Keeper Data'[Date] = _Date, 'Keeper Data'[Individual] = _Individual, 'Keeper Data'[Behavior] = _Behavior ), CALCULATE( SUM('Keeper Data'[BCOUNT]), 'Keeper Data'[Date] = _Date, 'Keeper Data'[Individual] = _Individual, ALL('Keeper Data'[Behavior]) ), 0 ) )

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

Re: Help with calculating an average daily % of lion behavior

v-dineshya — Fri, 26 Dec 2025 07:01:20 GMT

Hi KStout ,

Thank you for reaching out to the Microsoft Community Forum.

Hi lbendlin , tamerj1 and Praful_Potphode , Thank you for your prompt responses.

Hi KStout , Please try below measures.

Daily Behavior Count =
SUM ( 'Keeper Data'[BCOUNT] )

Daily Total Behaviors =
CALCULATE (
[Daily Behavior Count],
REMOVEFILTERS ( 'Keeper Data'[Behavior] )
)

Daily Behavior % =
DIVIDE (
[Daily Behavior Count],
[Daily Total Behaviors],
0
)

Mean Daily Behavior % =
AVERAGEX (
CROSSJOIN (
SUMMARIZE (
'Keeper Data',
'Keeper Data'[Date],
'Keeper Data'[Individual]
),
VALUES ( 'Keeper Data'[Behavior] )
),
CALCULATE ( [Daily Behavior %] )
)

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

Re: Help with calculating an average daily % of lion behavior

v-dineshya — Tue, 30 Dec 2025 05:02:42 GMT

Hi KStout ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

Re: Help with calculating an average daily % of lion behavior

v-dineshya — Fri, 02 Jan 2026 05:27:30 GMT

Hi @KStout ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh