Excel Pivot Table Measure Min Max Calculation

0
votes

Table example

Hi All,

I have a table that shows total attendance from July to September, there are various date entries for all.

I'm trying to find the max and min through a calculated measure in a pivot table. So far, I'm able to calculat:

  • the total attendance through a measure: =sum(Range[Count])
  • distinct day count through a measure: =distinctcount9[event date])
  • Daily average through a measure: =[total attendance/[dinstinct day count]

however, I'm trying to calculate the Max & Min through a measure since the pivot sort option does not calculate it. When I try =MAXX(RANGE, RANGE[TOTAL ATTENDANCE]) it just returns all 1s which for all months, which should not be the case. Any clues as to what should be the better formula?

Many thanks!

1
excelpivot-tableformuladax
To get more context: do you know if you are using Power Pivot? Is the data in a data model? When you say that the calculation is through a measure, do you mean a measure in the data model, or a function/calculated item in a pivot table? – Mistella
yes, it's in Power Pivot and it's in the data model. Measure in the data model that is added as a calculated field in a pivot table. However, I just found the solution: =MAXX(DISTINCT(Range[Event Date]),Range[Total Attendance]) – paonalytics

1 Answers

0
votes

=MAXX(DISTINCT(Range[Event Date]),Range[Total Attendance])

=MINX(DISTINCT(Range[Event Date]),Range[Total Attendance])