Calculate Highest Count Based on Category

Question

I have data detailing complaints per department and per category and I would like to add a Card showing Year Month with highest number of complaints. Eg: If Transport Services had highest number of all time with 30 complaints in July 2021.

Step 1) I added a column to show Year Month

Step 2) I have created a Measure to Count all the Complaints

D = COUNT(Complaints[Directorate])

Step 3) I created a Measure to show highest number of Complaints but not sure how to tell it that I want it to add up the complaints per department.

Highest Complaints Month Year = FIRSTNONBLANK(TOPN(1,VALUES('Complaints'[Year Month]),[d]),1)

Record Type Record Number Department Directorate Date

Complaint	COM/22/17	Compliance	Planning & Environment	18/12/2022
Complaint	COM/22/16	Transport Services	Infrastructure	15/11/2022
Complaint	COM/22/15	Water Services	Infrastructure	26/10/2022
Complaint	COM/22/14	Transport Services	Infrastructure	19/10/2022
Complaint	COM/22/13	Compliance	Planning & Environment	11/10/2022
Complaint	COM/22/12	Transport Services	Infrastructure	10/10/2022
Complaint	COM/22/11	Governance & Risk	Corporate Services	26/09/2022
Complaint	COM/22/10	Parks & Open Spaces	Planning & Environment	7/09/2022
Complaint	COM/22/9	Compliance	Planning & Environment	1/08/2022
Complaint	COM/22/8	Transport Services	Infrastructure	11/07/2022
Complaint	COM/22/6	Compliance	Planning & Environment	29/06/2022
Complaint	COM/22/5	Building Services	Planning & Environment	16/06/2022
Complaint	COM/22/4	Transport Services	Infrastructure	9/06/2022
Complaint	COM/22/2	Water Services	Infrastructure	18/05/2022
Complaint	COM/22/1	Parks & Open Spaces	Planning & Environment	16/02/2022
Complaint	COM/21/16	Compliance	Planning & Environment	20/12/2021
Complaint	COM/21/15	Customer Service	Community & Economic Development	15/12/2021
Complaint	COM/21/14	Customer Service	Community & Economic Development	14/12/2021
Complaint	COM/21/13	Waste & Environment	Planning & Environment	25/11/2021
Complaint	COM/21/12	Planning & Environment Directorate	Planning & Environment	23/11/2021

anonymous · Accepted Answer

Hi&nbsp;littlepatos&nbsp;,
&nbsp;
Please try this:
Measure = 
VAR __counts = MAXX(SUMMARIZE('Calendar','Calendar'[Year-Month],'Complaints'[Department]),CALCULATE(COUNTROWS('Complaints')))
VAR __month = MAXX(FILTER(SUMMARIZE('Calendar','Calendar'[Year-Month],'Complaints'[Department],"count",[D]),[count]=__counts),'Calendar'[Year-Month])
VAR __department = MAXX(FILTER(SUMMARIZE('Calendar','Calendar'[Year-Month],'Complaints'[Department],"count",[D]),[count]=__counts),'Complaints'[Department])
RETURN
__department &amp; " had highest number of all time with " &amp; __counts &amp; " complaints in " &amp; __month

Best Regards,GaoCommunity Support Team
&nbsp;
If there is any post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;&nbsp;to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know.&nbsp;Thanks a lot!
How to get your questions answered quickly&nbsp;--&nbsp;&nbsp;How to provide sample data in the Power BI Forum

Forum Discussion

Calculate Highest Count Based on Category

2 Replies

Related content

highest value by category

Filter only highest value by category

Calculate Average per category

calculate max per category.

Calculating cumulative total across categories