Max of count for each month

[BI_Dev]

Month	Weekday	# Distinct COUNT	Max OUTPUT
Dec 2024	Tue	982	982
Dec 2024	Tue	968
Nov 2024	Wed	931	931
Nov 2024	Tue	888
Oct 2024	Tue	915	915
Oct 2024	Tue	894

I need the max output in the chart by Month and I have the below expression 

max(aggr(count(distinct [ID]),[Month])) and it is not working

[rubenmarin]

Hi, you can use: Max(TOTAL <Month> [# Distinct COUNT])

Or, if you want to show the values only on the max value row: If(Max(TOTAL <Month> [# Distinct COUNT])=[# Distinct COUNT],Max(TOTAL <Month> [# Distinct COUNT]))

[PrashantSangle]

If you want to do same in script then use Window ()

Load *, Window(Count(ID),Month) as max_month_count

[Chanty4u]

Try this 

Max(Aggr(Count(Distinct [ID]), [Month], [Weekday]))

[BI_Dev]

@Chanty4u @rubenmarin @PrashantSangle That did not work - expressions gave the below result - 

Max number of sales is 982.

Now I want to show Max # of sales for each month - her for Dec 982 and Nov 931.

Expression for Mac overall is 

max(total aggr(count(distinct [ID]),[Date]))

[poklegoguy]

Try this:

IF(AGGR(count(distinct [ID]),Month,Weekday) = MAX(TOTAL <Month> AGGR(count(distinct [ID]),Month,Weekday)), count(distinct [ID]))

[PrashantSangle · Accepted solution]

using @rubenmarin solution

max(total<Month> aggr(count(distinct [ID]),[Date])).

Regards,

Prashant Sangle

[BI_Dev]

@PrashantSangle - Thank you. That worked.