locked
Calculate Week Average from a measure that calculate daily maximun transactions RRS feed

  • Question

  • Hoping someone has a number of ideas or a solution.

    The requirement I have is to calculate a weekly average from a daily max transaction calculation for each day.
    The following measures are working:
    MaxTrans:=MAX(TransactionTable[Transactions])
    MaxTransOnDay:=MAXX(VALUES(DimDate[Date]),[MaxTrans])
    MaxTransOnCurrentWeek:=CALCULATE([MaxTrans],FILTER(All(DimDate), DimDate[WeekOfYearNumber] = MIN(DimDate[WeekOfYearNumber])))

    A sample of the transaction table looks like:
    User DateTime Transactions
    G1 4/25/2017 9:00:00 AM 30
    G2 4/25/2017 9:00:00 AM 45
    G1 4/25/2017 9:15:00 AM 40
    G1 4/25/2017 10:00:00 AM 33
    G2 4/26/2017 9:00:00 AM 39
    G1 4/26/2017 11:00:00 AM 18

    The Pivot looks like: Filtering User, Week, and Day (M-F)
    User Date MaxTransOnDay MaxTransOnWeek
    G1 4/25/2017 40 40
    G1 4/26/2017 18 40

    What I am struggling with is the MaxAvgWeek:
    User Date MaxTransOnDay MaxTransOnWeek MaxAvgWeek
    G1 4/25/2017 40 40 29
    G1 4/26/2017 18 40 29

    MAxAvgWeek:=CALCULATE(AVERAGEX(Transactions,[MaxTransDay]),FILTER(ALL(Transactions),Transactions[WeekNum]=MAX(Date[WeekNum])))

    Thanks!

    Thursday, April 27, 2017 10:25 PM

Answers

  • Hi DRosencrans,

    Your result should be as follows, it doesn't return expected result.
    User Date MaxTransOnDay MaxTransOnWeek
    G1 4/25/2017 40 40
    G1 4/25/2017 45 40

    For MaxAvgWeek, please try the following formula.

    MAxAvgWeek:=SUMX(Transactions,[MaxTransDay])/COUNTROWS(SUMMARIZE(Transactions,Transactions[User]))

    Please feel free if you have any other issue.

    Best Regards,
    Angelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Friday, April 28, 2017 9:27 AM