Help: maximum from each count

[anson_kong]

Dear all,

I would like to set the formula to get the maximum from each count as below.

Result

Type	EachCount
ABC	13

can anyone share the idea?

Thank you so much

Table column 

Type (database column)	Order Date (database column)	EachCount (it is calculated measure, not in the database)
ABC	29-Sep-2017	13
ABC	19-Oct-2017	12
ABC	03-Oct-2017	11
ABC	21-Oct-2017	10
ABC	10-Oct-2017	9
ABC	12-Oct-2017	9
ABC	17-Oct-2017	9
ABC	20-Oct-2017	9
ABC	23-Oct-2017	9

[Chanty4u]

how the count is derived?   how you are getting 13 and all that numbers?

[OmarBenSalem]

max(total <Type> YourCount)

[Chanty4u]

you can try

=firstsortedvalue(type,-Eachcount)

[sunny_talwar · Accepted solution]

What is the expression for Count?

Try this

Dimension

Type

Expression

Max(Aggr(YourCountExpressionHere, Type, [Order Date]))

If you want this where both Type and Order Date are dimensions, then try this

Max(TOTAL <Type> Aggr(YourCountExpressionHere, Type, [Order Date]))

[anson_kong]

Thank you all,

I forget post the expression of my count.

[anson_kong]

Dear all,

I don't know how to modified the expression using Aggr () you provided to me.

here is my explanation, there are number transactions in order date by ID by order date.

I need to find the maximum unique ID exists in the particular period by Order Date.

in the below table, the maximum ID by type exists in 21-Oct-2017, as the total unique ID  is 189.

can you share the expression again?

thank you so much

Order Date (Database column)	Type (Database column)	count(distinct [ID]) (it is a calculated measure)
29-Sep-2017	CCC	13
29-Sep-2017	EEE	4
29-Sep-2017	BBB	156
30-Sep-2017	CCC	8
30-Sep-2017	EEE	15
30-Sep-2017	BBB	143
21-Oct-2017	CCC	10
21-Oct-2017	EEE	24
21-Oct-2017	BBB	155

Wanted result

Type	count(distinct [ID])
CCC	10
EEE	24
BBB	155
Total	189

[sunny_talwar]

You can try one of these

FirstSortedValue(Aggr(Count(DISTINCT ID), [Order Date], Type), -Aggr([Order Date], [Order Date], Type))

or if each type will always have the same max date, then this

Count(DISTINCT {<[Order Date] = {"$(=Max([Order Date]))"}>} ID)

[anson_kong]

Thank you Sunny,