Forum Discussion
Calculate daily maximums across filtered data
Hi there,
Problem
We have slicers to filter by Date hierarchy, Type/Item, Region, Shop, and Seller.
We need to be able to display the total sales as well as daily maximum sales for the chosen filters.
Displaying the total sales figure is simple on a line chart, however filters don't work on the daily maximum.
When I say daily maximum, I mean the maximum value per day for the sum of sales within that day, even if we have drilled up the date hierarchy and are viewing by week or year, while also respecting any filters selected within the slicers.
Example data is below (I have a slightly expanded example PBIX file but not sure how to share/if sharing is possible here):
ItemsByTransactionID
| Transaction ID | Quantity | Item | Transaction date |
| 1 | 4 | Apples | 01/01/2001 |
| 1 | 9 | Oranges | 01/01/2001 |
| 1 | 9 | Pears | 01/01/2001 |
| 2 | 7 | Oranges | 09/01/2001 |
| 2 | 5 | Pears | 09/01/2001 |
| 2 | 7 | Apples | 09/01/2001 |
| 3 | 5 | Pears | 09/01/2001 |
| 3 | 9 | Oranges | 09/01/2001 |
| 3 | 7 | Apples | 09/01/2001 |
Transactions
| Transaction ID | Region | Shop | Seller |
| 1 | North | Durham | A |
| 2 | North | Carlisle | B |
| 3 | South | Brighton | C |
Calendar
| Date | Year | Week |
| 01/01/2001 | 2000/2001 | 40 |
| 02/01/2001 | 2000/2001 | 40 |
| 03/01/2001 | 2000/2001 | 40 |
| 04/01/2001 | 2000/2001 | 40 |
| 05/01/2001 | 2000/2001 | 40 |
| 06/01/2001 | 2000/2001 | 40 |
| 07/01/2001 | 2000/2001 | 40 |
| 08/01/2001 | 2000/2001 | 41 |
| 09/01/2001 | 2000/2001 | 41 |
Items
| Type | Item |
| Fruit | Apples |
| Fruit | Pears |
| Citrus | Oranges |
How do I go about doing this? I've tried to be clear while remaining concise, if anything doesn't make sense or any more info is needed to help then please let me know.
Thanks very much for any help!
If you already have your Total Sales measure then you can calculate the maximum daily amount with
Daily max sales = MAXX ( VALUES ( 'Date'[Date] ), [Total Sales] )Should work at any level of the date hierarchy.
For future reference, when people want to share PBIX files they tend to use a file sharing site like Dropbox or We Transfer, or else share directly from something like Google Drive or OneDrive.
4 Replies
- onurbmiguel_
Power Participant
hello paddysellsfruit
Can you share what will be the pretended result?
You can share by onedrive link or google drive link.
BR
Bruno
johnt75Super User
If you already have your Total Sales measure then you can calculate the maximum daily amount with
Daily max sales = MAXX ( VALUES ( 'Date'[Date] ), [Total Sales] )Should work at any level of the date hierarchy.
For future reference, when people want to share PBIX files they tend to use a file sharing site like Dropbox or We Transfer, or else share directly from something like Google Drive or OneDrive.
Legend, thank you! I've spent days Googling around for solutions and trying needlessly complex things, I knew it would be something much more simple.
HoangHugoSolution Specialist
Which charts be use for your daily maximum sales? and daily maximum sales mean, which days have highest sales? or Which items have highest sales in one day?
