MAX Measure showing as a SUM on graph

[mollycat]

Hello!

I have the following dataset where the Time_Slice_10 represents during which 10-minute period the event occurred. The Time_Slice_10_Count then totals the number of events that happened during that time. Note: in this example, the total is grouped by Facility.

Facility	Event_Time	Time_Slice_10	Event_Date	Time_Slice_10_Count (calculated)
A	2022-05-21 07:54:07	2022-05-21 07:50:00	2022-05-21	2
A	2022-05-21 07:58:16	2022-05-21 07:50:00	2022-05-21	2
A	2022-07-12 19:24:07	2022-07-12 19:20:00	2022-07-12	1
A	2022-07-15 03:45:00	2022-07-15 03:40:00	2022-07-15	1

In Power BI, I am using the following Measures to calcuate the Time_Slice_10_Count:

• CALC1 = COUNT('Database.Table'[Time_Slice_10])

• CALC2 = MAXX(

    KEEPFILTERS(VALUES('Database.Table'[Event_Date])),

    CALCULATE([CALC1]))

The problem I am running into is that on my graph I want to see the MAX value of the Time_Slice_10_Count per day, but the Measure is summing to show me a total of 4 for the date of 2022-05-21, instead of 2. Since it is a Measure, I cannot change the aggregation type in the visualization like I could with a Column. I am trying to do these calculations as Measures so that the values can be dynamic depending on other filtered values on the page. For example, in this example I am grouping by the Facility, but I'd also like the option to group and show MAX values by Area, Region, or any other filter. My understanding is that if I calculate via Columns, I need to indicate how to do the slicing and dicing in the calculation itself rather than using the page filters.

Any advice on this is greatly appreciated!

[v-yangliu-msft · Accepted solution]

Hi  @mollycat ,

Here are the steps you can follow：

1. Create measure.

Time_Slice_10_Count (calculated) =
var _10min=MAX('Table'[Time_Slice_10]) +TIME(0,10,0)
return
COUNTX(FILTER(ALL('Table'),'Table'[Facility]=MAX('Table'[Facility])&&'Table'[Event_Time]<=_10min&&'Table'[Event_Time]>='Table'[Time_Slice_10]&&'Table'[Event_Date]=MAX('Table'[Event_Date])
),[Facility])

Measure =
MAXX(FILTER(ALL('Table'),'Table'[Facility]=MAX('Table'[Facility])&&'Table'[Event_Date]=MAX('Table'[Event_Date])),[Time_Slice_10_Count (calculated)])

2. Result:

If you need pbix, please click here.

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

[amitchandak]

@mollycat ,

Calculate(Sumx(Summarize(Table, Table[Facility], Table[Event_Date]), Calculate(Lastnonblankvalue(Table[Event_Date]), Count(Table[Event_Date]) ) ) , allexcept(Table, Table[Facility], Table[Event_Date]) )

or check if this can help

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[mollycat]

Hi @amitchandak, thank you for your response. Unfortunately this formula is still giving me the SUM of the values, rather than the MAX. I've written this measure which seems to get me pretty close but is also not completely working:

Measure = MAXX('Table',COUNT('Table'[Time_Slice_10])))

This measure correctly calculates the number of Time_Slice_10 events, but then it does not aggregate properly to calculate the MAX value. Example:

Facility	Event_Date	Time_Slice_10	Measure
A	2022-07-19	2022-07-19 20:00:00	1
A	2022-07-19	2022-07-19 05:10:00	4
A	2022-07-19	2022-07-19 05:50:00	1
A	2022-07-19	2022-07-19 06:40:00	11
A	2022-07-19	2022-07-19 07:10:00	2

Based on the data in the table this Measure is calculating correctly (ie: there are 11 rows of events that fall into the 2022-07-19 06:40:00 time slice, two events in the 2022-07-19 07:10:00 time slice, etc.). However, when I remove the Time_Slice_10 column, I expect the Measure to recalculate based on the columns in the table to show me the MAX value of 11. Instead it does the sum of these and returns 19 (1+4+1+11+2):

Facility	Event_Date	Measure
A	2022-07-19	19

Again, I really appreciate your help and response on this! 🙂