Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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[Transactions])
MaxTransOnDay:=MAXX(VALUES(DimDate[Date]),[MaxTrans])
MaxTransOnCurrentWeek:=CALCULATE([MaxTrans],FILTER(All(DimDate), DimDate[WeekOfYearNumber] = MIN(DimDate[WeekOfYearNumber])))
AvgOfMaxTransDay:=?
Data looks like:
MaxTransOnDay | MaxTransOnWeek | AvgOfMaxTransDay | |
Rover | |||
Week 15 | |||
4/14/2017 | 70 | 75 | 51 |
4/13/2017 | 75 | 75 | 51 |
4/12/2017 | 50 | 75 | 51 |
4/11/2017 | 30 | 75 | 51 |
4/10/2017 | 30 | 75 | 51 |
Week 14 | |||
4/7/2017 | 90 | 90 | 46 |
4/6/2017 | 40 | 90 | 46 |
4/5/2017 | 23 | 90 | 46 |
4/4/2017 | 33 | 90 | 46 |
4/3/2017 | 44 | 90 | 46 |
Thanks for any direction/assistance!
Doug
Solved! Go to Solution.
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
Hi @drosencrans,
You can build a relationship between the Date table and fact table use date column field, then create a measure like below:
AvgOfMaxTransDay = CALCULATE(AVERAGE(Table2[Transactions]),FILTER(ALL(Table2),'Table2'[Weeknum]=MAX('Date'[WeekNum])))
Best Regards,
Qiuyun Yu
Hello Qiuyun Yu,
Thanks for the assist. My data and model are at a different granularity.
I can have multiple date (datetime) transansactions. Need 3 measures
1. MaxTransDay = MAX(Transactions[Transactions])
2. MaxTransWeek = CALCULATE(MAX(Transactions[Transactions]),FILTER(ALL(Transactions),'Transactions'[Weeknum]=MAX('Date'[WeekNum])))
3. AvgMaxWeek = CALCULATE(AVERAGEX(Transactions,[MaxTransDay]),FILTER(ALL(Transactions),Transactions[WeekNum]=MAX('Date'[WeekNum])))
AvgMaxWeek is calculating the average over the entire week and not the MaxTransDay. Need the Average of the MaxTransDay.
URL for sample:
https://1drv.ms/u/s!AjYWvmK-s42rawdKD2MUjezVUsw
Thanks again for the additonal support!
Doug
Hi @drosencrans,
The URL you shared is not accessible. Would you please sample about the requirement "AvgMaxWeek is calculating the average over the entire week and not the MaxTransDay. Need the Average of the MaxTransDay."?
Best Regards,
Qiuyun Yu
Odd - OneDrive blocked the account. Support said that this should be fixed tomorrow.
In the meantime, I am able to calculate the Max (top) transaction for the day. Each day has multiple transactions per hour. I calculate the Max for that day. I also calculate the Max for the week. I also have a need to calculate the Average for the week based on each day's Max. I do a number of other calculations for which day, hour had the max.
Just need to figure-out how to get the average from the Max Each Day.
Example:
For week 1:
Day 1 Max = 55
Day 2 Max = 44
Day 3 Max = 55
Day 4 Max = 70
Day 5 Max = 30
{I exclude/filter out Sat and Sun, Day 6 and 7}
Average for the Week = 51
Thanks,
Doug
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
Hello Qiuyun Yu,
This is an interesting solution that works for Power BI Desktop. Is there a comparable for PowerPivot using DAX as I often model in both?
Thanks,
Doug
Hi @drosencrans,
As both Power BI desktop and PowerPivot support DAX, the solution I shared should also work in PowerPivot. If you have any question regarding PowerPivot, you can pot a thread here to get dedicated support.
From your description, it seems the original issue is solved, would you please mark a helpful reply as an answer so that we can close the thread?
Best Regards,
Qiuyun Yu
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |