How to make the total average number of days appear?

[MarianoV]

Hi! I have a measure where it calculates the average number of days from the user's previous purchase date to the next purchase date. However, the total does not appear in the table. I have another measure which on the other hand, calculates the average number of days from the user's first session to their first purchase and there is a total for that. However, I cannot do it for the other measure. Here's my calculation for AvgDaysBeforePurchase.

AvgDaysBeforePurchase =

VAR CurrentDate = SELECTEDVALUE('Transactions'[Date])

VAR PrevDate = CALCULATE(MAX('Transactions'[Date]),FILTER(ALL('Transactions'),'Transactions'[Date] < CurrentDate && 'Transactions'[user_pseudo_id] = SELECTEDVALUE('Transactions'[user_pseudo_id])))

RETURN

    AVERAGEX(

        VALUES('Transactions'[user_pseudo_id]),

        DATEDIFF(

            IF(ISBLANK(PrevDate), SELECTEDVALUE('Transactions'[firstSessionDate]), PrevDate),

            CurrentDate,

            DAY

        )

    )

[MarianoV]

Hi! It worked but taking so long to load, and i dont know why, but when I use other rows (other than userID), values are not showing.

What I did instead is I created a calculated column, then added it in the table as Average. Would that suffice? 

P.S. Blanks are those users that do not have other purchases, whereas those with values in the Average of DaysBeforePurchase are based from the same user's previous transaction.

[Anonymous]

Hi  @MarianoV ,

The Total total value will change with the formula in the measure, it could be that your formula is setting the total value to null, you can create a measure and use IF() + HASONEVALUE() to determine if it's total, if it is, customize the aggregation rule, if it's not, then display [AvgDaysBeforePurchase].

AvgDaysBeforePurchase =
IF(
    HASONEVALUE('Transactions'[user_pseudo_id]),MAX('Transactions'[Value]),BLANK())

If the results do not meet your desired outcome, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.