Return values on day of other maximum values

[PaulHallam]

Hi All,

I have posted something similar but had no luck, so i have attached a link to power bi project in the hope it makes sense.

The graph shows the maximum count of "high" &"low to medium" events on any day/any month (per day) as an orange diamond on graph.
The graph shows the maximum count of "high" &"low to medium" events per sliced month (per day) as a red line on graph.

Orange bar should be the count of "low to medium" on the day of max sliced month (per day)

Pink bar should be the count of "high" on the day of max sliced month (per day)

If you look at Wednesdays in August in the table then the maximum "high" & "low to medium" is 1,124 with "high" of 802 and "low to medium" of 322
But the graph shows a medium of 352 for "low to medium" because its higher than the 322
I need a way to return the bar totals on the day of maximum count of "high" &"low to medium" events per sliced month (per day)
Any thoughts? Project link below image

https://www.dropbox.com/scl/fi/8n4jj9ht53i5dn1xf0f39/Forum.pbix?rlkey=w67x1pu1moslrrtulx84hicj9&st=j...

Thanks

[Anonymous · Accepted solution]

Hi  @PaulHallam ,

Here are the steps you can follow：

1. Create measure.

MbyDate =
var _table1=
SUMMARIZE(ALLSELECTED('DateTable'),[Day],[Date],"CountH&M",[CountH&M],"CountM",[CountM])
var _table2=
ADDCOLUMNS(
    _table1,"maxcount",MAXX(FILTER(_table1,[Day]=MAX([Day])),[CountH&M]))
RETURN
MAXX(
    FILTER(
        _table2,[Day]=MAX('DateTable'[Day])&&[maxcount]=[CountH&M]),[CountM])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[PaulHallam]

Brilliant thank you so much

[Anonymous]

Hi  @PaulHallam ,

Based on my research, in the line and clustered column chart visual, [MbyDate] shows the highest "low to medium" count for each day in the slicer month. In August 2024, there are two Wednesdays, so it retrieves the highest "low to medium" count of 352 for those two Wednesdays. The table visual, however, displays all values instead of taking the highest "low to medium" count.

I'm not entirely clear on your final requirement. According to your description, [MaxH&MAllMonths] is the sum of "high" & "low to medium." If you want to display it on the bar, you can directly place [MaxH&MAllMonths] on the column y-axis.

If the above description does not meet your expected results, you can express the expected results in the form of a picture, we can better help you!

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulHallam]

Hi Liu Yang, thank you for looking at this.

The only visual i am interested in is the Bar & Line Chart. The table is for reference.

The values circled in green on the picture are correct and show the highest count of "High" & "Low to Medium" in the sliced month.
The values circled in orange on the picture are correct and shows the highest count of "High" & "Low to Medium" in any month.
I need the bars to show the values of "High" and "Low to Medium" on the day of the highest count of "High" & "Low to Medium" in the sliced month. So the red circled number in the bar should read 322 not 352 as the highest count of "High" & "Low to Medium" in the sliced month for Wednesdays was 1,124 on 28th August, the value of "Low to Medium" on this date was 322

Thanks