Help with calculating an average daily % of lion behavior

[KStout]

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))

[Praful_Potphode · Accepted solution]

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

[v-dineshya · Accepted solution]

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

[v-dineshya]

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

[v-dineshya]

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

[Praful_Potphode · Accepted solution]

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

[tamerj1]

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] ) )
)
)

[KStout]

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

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...