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.
I'm trying to get the date for the maximum summed volume over a period of time and then display the returned summed max along with the components from that formula. I can successfully get the date and the max value but when I go to show all of the components that make up the maximum summed value, the measure applies the date for each components individual maximum sum. I cannot, for the life of me, find a way to make the date only take the date from the summed whole.
date | prod | qty |
2/27/2023 | a | 15 |
2/27/2023 | b | 5 |
2/27/2023 | c | 2 |
2/27/2023 | a | 15 |
2/27/2023 | b | 10 |
2/27/2023 | c | 5 |
2/28/2023 | a | 15 |
2/28/2023 | b | 10 |
2/28/2023 | c | 10 |
2/28/2023 | a | 10 |
2/28/2023 | b | 20 |
2/28/2023 | c | 10 |
Currently | ||
2/28/2023 | 75 | total |
30 | a | |
30 | b | |
20 | c | |
Expected | Max Value total | |
2/28/2023 | 75 | total |
25 | a | |
30 | b | |
20 | c |
Attached is a dumbed down version of my dataset. I do have a separate 'Dates' table and I currently have a measure that is summing the individual prod values.
Dangit, my mistake (copy paste error).
You're right, it should be 02/28/23. I made the correction and reposted. Thanks.
Hi,
You may download my PBI file from here.
Hope this helps.
Thank you! Problem is I can't get to that link. Is it possible to post the measure(s) or possibly email the file?
I think it's close but the Max Date measure formula takes the max date of whatever dates column is put into it no matter what the corresponding filters are past that. I tried it with two separate date column information and each one always put out the max date of the dates.
Not clear about what you want. Share the PBI file and show the the problem there clearly. Also show the expected result.
I expanded the data on the file you sent and added the measures I am currently trying to correct. I can find the date of the max total sum of individual products for a given day. The problem is, when I try to go isolate this specific day's total values and list summed product totals and the overall total, the product values take their individual summed maximum date values (and date) instead of the maximum summed total date and the corresponding product totals for that specific date.
It seems I don't have the right permissions to attach the file and I have no ability to provide a link so please bare with me and forgive the screenshots and text.
I added some more rows to the existing table:
date | prod | qty |
27-02-2023 | a | 15 |
27-02-2023 | b | 5 |
27-02-2023 | c | 2 |
27-02-2023 | a | 15 |
27-02-2023 | b | 10 |
27-02-2023 | c | 5 |
28-02-2023 | a | 15 |
28-02-2023 | b | 10 |
28-02-2023 | c | 10 |
28-02-2023 | a | 10 |
28-02-2023 | b | 20 |
28-02-2023 | c | 10 |
01-03-2023 | a | 5 |
01-03-2023 | a | 10 |
01-03-2023 | b | 5 |
01-03-2023 | b | 5 |
01-03-2023 | c | 5 |
01-03-2023 | c | 5 |
02-03-2023 | a | 10 |
02-03-2023 | b | 5 |
02-03-2023 | c | 5 |
03-03-2023 | a | 5 |
03-03-2023 | b | 5 |
03-03-2023 | c | 5 |
My Measure to find the date with the highest total summed Product Value:
THTermMax_Date =
CALCULATE(
MAXX(
TOPN(1,
SUMMARIZE('Calendar','Calendar'[Date],"Max Vol",[Quantity]),
[Max Vol]),
'Calendar'[Date]
),
REMOVEFILTERS('Calendar'[Date])
)
Measure to display the Quantities from the date found above:
THTermMax_Vol =The date works, but you'll notice the in the Matrix/Table that the values do not correctly add up. This is because "a" total is using its max value from 02/27/2023 instead of using the day's total from 02/28/2023. I am expecting the "a" total to be 25 which is the total from 02/28. This is not isolated to "a" either. If I change the values on different days and make them the maximum for that product, the maximum product total will show regardless of the max total for the entire day.
It is a lot easier to help if i have the file. Share the download link of the PBI file. Show the result there clearly.
As mentioned before, I have no ability to share the link per my company, which is regretful. All of the measures shown in the previous post and the additions to the table were added to the file you provided earlier. That's the best I can do given my restrictions.
I'm still working on this with no solution yet. I would be happy to send the file you had previously attached with my updates if you could send an email address. I just have no other way to share the file.
Hi,
The total quantity on Feb 27 is 52 and on Feb 28 is 75. In the result why are you showing Feb 27 then?
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 |
---|---|
96 | |
95 | |
82 | |
71 | |
64 |
User | Count |
---|---|
116 | |
106 | |
94 | |
79 | |
72 |