Forum Discussion

drosencrans's avatar
drosencrans
Frequent Visitor
9 years ago
Solved

Calculate Average of Maximum Transactions Per Day by Week

Hello Power BI Community,   My scenerio:  calculate the weekly average based on the maximum # transactions per datetime. I have the following measures working: MaxTrans:=MAX(TransactionTable[Tran...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi drosencrans,

     

    You can create a calculated table to return maximum transactions per day in one column,

     

    Table = SUMMARIZE('Table2','Table2'[Date],'Table2'[Weeknum],"MaxPerDay",MAX('Table2'[Transactions]))

     

    Build a relationship between this new table with Date table, then create a measure in this new table like this:

     

    Measure = CALCULATE(AVERAGE('Table'[MaxPerDay]),FILTER(ALL('Table'),'Table'[Weeknum]=MAX('Date'[WeekNum])))

     

    Please download the attached .pbix to see if the issue meet your requirements.

     

    Best Regards,
    Qiuyun Yu

drosencrans's avatar
drosencrans
Frequent Visitor
9 years ago

Hey All!

 

I realized that I have been caclulting my Average correctly:

WeeklyAvg:=CALCULATE([AverageXOfMP],FILTER(DimDate, DimDate[WeekNumberofYear]))

 

The problem I am having are wil the filters being applied as part of the pivot are not adhered to when I exclude Sat and Sun.  I need to change the filter context, but not sure why the day filter is being removed.

 

Any idea where I should look?

 

Thanks,

Doug