How COUNT number MAX Values in column?

[citizenbh]

Sample:
I have count number max values by Type

Table:

Name	Type	Value
a	1	100
b	1	67
c	1	100
d	1	88
e	1	35
f	2	200
g	2	123
h	2	178
i	2	200
j	2	200
k	3	198
l	3	300
m	3	256
n	3	248
o	3	180

<tbody>
</tbody>

________________________
Result:

Type	Count(Max)
1	2
2	3
3	1

<tbody>
</tbody>

---------------------------
Thx

 

[KHorseman]

Table[CountMax]
:=CALCULATE(
    COUNTROWS(Table),
    FILTER(
        Table,
        Table[Value] = MAX(Table[Value])
    )
)

 

[Ozeroth]

An alternative using LASTNONBLANK:

=CALCULATE(
    COUNTROWS(Table),
    LASTNONBLANK( Table[Value], 0)
)

 

[KHorseman]

Could you explain how that use of LASTNONBLANK works? I'm having trouble wrapping my head around it. Does LASTNONBLANK treat the given column as being in ascending numeric order automatically? I don't see anything about that in the documentation but otherwise I don't get how it would give you the max. I also don't get the use of 0 as the expression. But this looks like a pretty cool trick if I could see what makes it work.

 

[Ozeroth]

Sure,

• LASTNONBLANK will return the "last" value of the single-column table (or column) provided in the first argument, restricted to values where the second argument is nonblank.
• Last = max for numbers, or lexicographically last for strings etc, while MAX only works with numbers.
• The other difference to MAX is that LASTNONBLANK actually returns a single-row, single-column table containing the "last" value (This will be treated as a scalar or table depending on context).
  This allows LASTNONBLANK to be used as a filter argument in CALCULATE, for example.
• By using an arbitrary nonblank second argument like I did (e.g. 0 or 1), LASTNONBLANK is just like MAX but returns a table rather than a scalar.

SQLBI has a good page on FIRST/LASTNONBLANK:
https://www.sqlbi.com/articles/alternative-use-of-firstnonblank-and-lastnonblank/

 

[citizenbh]

Thanks guys!

I've got such a table

Type	Value	Sum of Table[CountMax]
1	100	2
	88	1
	67	1
	35	1
2	200	3
	178	1
	123	1
3	300	1
	256	1
	248	1
	198	1
	180	1
Grand Total		15

<tbody>
</tbody>

Is it possible to get a summary table above in the first post

 

[citizenbh]

Count the number different Values per group Type
=CALCULATE(DISTINCTCOUNT([Value]);FILTER(Table1;[Type]=EARLIER(Table1[Type])))

Account MAX Value per group Type
=CALCULATE(MAX([Value]);FILTER(Table1;[Type]=EARLIER(Table1[Type])))

When we put them in an area ΣValues all give the same result (ValueField Settings: Count) as previus posts,
but I can't get a summary table of the first post.

 

[Ozeroth]

You can get the summary table from the first post by using one of the Countmax measures (from posts 2-3) as long as you have Type but not Value as row fields.
Is that what you were getting at?

 

[citizenbh]

Exactly. Sorry, my mistake, the table should look like this:

Result:

Type	MAXVAL	Count(Max)
1	100	2
2	200	3
3	300	1

<tbody>
</tbody>

---------------------------

 

[citizenbh]

Ok, I solved

Type	MAXVAL	Table[CountMax]
1	100	2
2	200	3
3	300	1

<tbody>
</tbody>

Rows:		Type, MAXVAL
ΣValues:		Table[CountMax] - no calculation
DAX Column:		MAXVAL	=CALCULATE(MAX([Value]);FILTER('Table';[Type]=EARLIER('Table'[Type])))
		Table[CountMax]	- from post 2

<tbody>
</tbody>